<?php
/**
 * member actions.
 *
 * @package    sf_sandbox
 * @subpackage member
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 2692 2006-11-15 21:03:55Z fabien $
 */
class memberActions extends sfActions
{
    public function executeApplyVisaCard()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "APPLY_VISA");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "APPLY_VISA");

        return $this->redirect('/member/underConstruction');
    }
    public function executeApplyDealer()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "APPLY_DEALER");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "APPLY_DEALER");

        return $this->redirect('/member/underConstruction');
    }
    public function executeApplyClub()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "APPLY_CLUB");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "APPLY_CLUB");

        return $this->redirect('/member/underConstruction');
    }

    public function executeUnderConstruction()
    {

    }

    public function executeAccountSummary()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "MY_ACCOUNT");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "ACCOUNT_SUMMARY");

        $distributor = MlmDistributorPeer::retrieveByPK($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $this->forward404Unless($distributor);

        $mpBalance = 0;
        $mgBalance = 0;
        $mt4Balance = 0;
        $totalNetworks = 0;
        $ranking = "";
        $currencyCode = "RMB";

        if ($distributor) {
            $existUser = AppUserPeer::retrieveByPK($distributor->getUserId());

            if ($existUser) {
                $lastLogin = $existUser->getLastLoginDatetime();
            }

            $packageDB = MlmPackagePeer::retrieveByPK($distributor->getRankId());
            if ($packageDB) {
                $ranking = $packageDB->getPackageName();
            }

            $mpBalance = $this->getAccountBalance($distributor->getDistributorId(), Globals::ACCOUNT_TYPE_MP);
            $mgBalance = $this->getAccountBalance($distributor->getDistributorId(), Globals::ACCOUNT_TYPE_MG);
            $mt4Balance = $this->getAccountBalance($distributor->getDistributorId(), Globals::ACCOUNT_TYPE_MT4);

            $c = new Criteria();
            $c->add(MlmDistributorPeer::UPLINE_DIST_ID, $distributor->getDistributorId());
            $c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
            $totalNetworks = MlmDistributorPeer::doCount($c);
        }

        $this->mpBalance = $mpBalance;
        $this->mgBalance = $mgBalance;
        $this->mt4Balance = $mt4Balance;
        $this->totalNetworks = $totalNetworks;
        $this->ranking = $ranking;
        $this->colorArr = $this->getRankColorArr();
        $this->currencyCode = $currencyCode;
        $this->distributor = $distributor;
        $this->lastLogin = $lastLogin;
    }

    public function executeTransferMG()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "REGISTRATION");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "TRANSFER_MG");
    }

    public function executeRedeemMp()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "REGISTRATION");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "REDEEM_MP");
    }

    public function executeMgManagement()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "REGISTRATION");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "MG_MANAGEMENT");
    }

    public function executeGozSuccessRedirect()
    {
        //var_dump(date("Ymd"));
        //exit();
        //        print_r("<br>ver:".$this->getRequestParameter('ver'));
        //        print_r("<br>merid:".$this->getRequestParameter('merid'));
        //        print_r("<br>orderid:".$this->getRequestParameter('orderid'));
        //        print_r("<br>amount:".$this->getRequestParameter('amount'));
        //        print_r("<br>orderdate:".$this->getRequestParameter('orderdate'));
        //        print_r("<br>curtype:".$this->getRequestParameter('curtype'));
        //        print_r("<br>paytype:".$this->getRequestParameter('paytype'));
        //        print_r("<br>lang:".$this->getRequestParameter('lang'));
        //        print_r("<br>returnurl:".$this->getRequestParameter('returnurl'));
        //        print_r("<br>errorurl:".$this->getRequestParameter('errorurl'));
        //        print_r("<br>remark1:".$this->getRequestParameter('remark1'));
        //        print_r("<br>enctype:".$this->getRequestParameter('enctype'));
        //        print_r("<br>notifytype:".$this->getRequestParameter('notifytype'));
        //        print_r("<br>urltype:".$this->getRequestParameter('urltype'));
        //        print_r("<br>s2surl:".$this->getRequestParameter('s2surl'));
        //        print_r("<br>goodsname:".$this->getRequestParameter('goodsname'));
        //        print_r("<br>channelid:".$this->getRequestParameter('channelid'));
        //        print_r("<br>sign:".$this->getRequestParameter('sign'));

        $sign = $this->getRequestParameter("sign");
        $transtat = $this->getRequestParameter("transtat");
        $amount = $this->getRequestParameter("amount");

        $c = new Criteria();
        $c->add(MlmDistEpointPurchasePeer::PG_SIGNATURE, $sign);
        $mlmDistEpointPurchase = MlmDistEpointPurchasePeer::doSelectOne($c);

        /*mysql_select_db('dfff_a', $con);
        mysql_query("SET CHARACTER SET UTF8");
        $sql = "SELECT *  FROM `cn` WHERE `amount` = '" . $amount . "' AND `sign` = '" . $sign . "' LIMIT 1";
        $rs = mysql_query($sql);
        $count = count($rs);
        if ($coun != 1) {
            die("Valid result!");
        } else {
            if ($transtat == '000') {
                die("OK");
            } else {
                die("Valid result!");
            }
        }*/
        if ($mlmDistEpointPurchase) {
            if ($transtat == '000') {
                $dist = MlmDistributorPeer::retrieveByPK($mlmDistEpointPurchase->getDistId());
                $totalEpoint = $mlmDistEpointPurchase->getAmount();

                $mlmDistEpointPurchase->setPgSuccess("Y");
                $mlmDistEpointPurchase->setPgMsg("SUCCESS");
                $mlmDistEpointPurchase->setStatusCode(Globals::STATUS_COMPLETE);
                $mlmDistEpointPurchase->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID));
                $mlmDistEpointPurchase->setApproveRejectDatetime(date("Y/m/d h:i:s A"));
                $mlmDistEpointPurchase->setApprovedByUserid($this->getUser()->getAttribute(Globals::SESSION_USERID));
                $mlmDistEpointPurchase->save();

                if ($mlmDistEpointPurchase->getWalletType() == Globals::ACCOUNT_TYPE_EPOINT) {
                    $c = new Criteria();
                    $c->add(MlmIbPeer::DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
                    $c->add(MlmIbPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
                    $mlmIb = MlmIbPeer::doSelectOne($c);

                    if ($mlmIb) {
                        $topupBonus = $mlmIb->getTopupBonus();

                        $totalEpoint = $totalEpoint + ($totalEpoint * $topupBonus);
                    }
                }
                $distEpoint = $this->getAccountBalance($dist->getDistributorId(), $mlmDistEpointPurchase->getWalletType());

                $mlm_account_ledger = new MlmAccountLedger();
                $mlm_account_ledger->setDistId($dist->getDistributorId());
                $mlm_account_ledger->setAccountType($mlmDistEpointPurchase->getWalletType());
                $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_POINT_PURCHASE);
                $mlm_account_ledger->setRemark("");
                $mlm_account_ledger->setInternalRemark("Refer Id:" . $mlmDistEpointPurchase->getPurchaseId());
                $mlm_account_ledger->setCredit($totalEpoint);
                $mlm_account_ledger->setDebit(0);
                $mlm_account_ledger->setBalance($distEpoint + $totalEpoint);
                $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_account_ledger->save();

                $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Transaction Successful."));
                return $this->redirect('/member/fundsDeposit?pg=Y');
            } else {
                $mlmDistEpointPurchase->setPgSuccess("N");
                $mlmDistEpointPurchase->setPgMsg("transtat not 000");
                $mlmDistEpointPurchase->setStatusCode(Globals::STATUS_REJECT);
                $mlmDistEpointPurchase->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID));
                $mlmDistEpointPurchase->setApproveRejectDatetime(date("Y/m/d h:i:s A"));
                $mlmDistEpointPurchase->setApprovedByUserid($this->getUser()->getAttribute(Globals::SESSION_USERID));
                $mlmDistEpointPurchase->save();

                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Invalid Action."));
                return $this->redirect('/member/fundsDeposit');
            }
        } else {
            $mlmDistEpointPurchase->setPgSuccess("N");
            $mlmDistEpointPurchase->setPgMsg("Invalid Signature");
            $mlmDistEpointPurchase->setStatusCode(Globals::STATUS_REJECT);
            $mlmDistEpointPurchase->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID));
            $mlmDistEpointPurchase->setApproveRejectDatetime(date("Y/m/d h:i:s A"));
            $mlmDistEpointPurchase->setApprovedByUserid($this->getUser()->getAttribute(Globals::SESSION_USERID));
            $mlmDistEpointPurchase->save();

            $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Invalid Action."));
            return $this->redirect('/member/fundsDeposit');
        }
        return sfView::HEADER_ONLY;
    }

    public function executeGozErrorRedirect()
    {
        return sfView::HEADER_ONLY;
    }

    public function executeFundsDeposit()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "REGISTRATION");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "FUNDS_DEPOSIT");

        $c = new Criteria();
        $c->add(MlmIbPeer::DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $c->add(MlmIbPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $this->mlmIb = MlmIbPeer::doSelectOne($c);

        $distDB = MlmDistributorPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        if (!$distDB)
            return $this->redirect('/home/logout');
        $this->distDB = $distDB;

        $this->tradingCurrencyOnMT4 = "RMB";
        $this->systemCurrency = $this->getAppSetting(Globals::SETTING_SYSTEM_CURRENCY);

        /*$this->bankName = $this->getAppSetting(Globals::SETTING_BANK_NAME);
        $this->bankSwiftCode = $this->getAppSetting(Globals::SETTING_BANK_SWIFT_CODE);
        $this->iban = $this->getAppSetting(Globals::SETTING_IBAN);
        $this->bankAccountHolder = $this->getAppSetting(Globals::SETTING_BANK_ACCOUNT_HOLDER);
        $this->bankAccountNumber = $this->getAppSetting(Globals::SETTING_BANK_ACCOUNT_NUMBER);
        $this->cityOfBank = $this->getAppSetting(Globals::SETTING_CITY_OF_BANK);
        $this->countryOfBank = $this->getAppSetting(Globals::SETTING_COUNTRY_OF_BANK);

        $this->bankName2 = $this->getAppSetting(Globals::SETTING_BANK_NAME_2);
        $this->bankSwiftCode2 = $this->getAppSetting(Globals::SETTING_BANK_SWIFT_CODE_2);
        $this->iban2 = $this->getAppSetting(Globals::SETTING_IBAN_2);
        $this->bankAccountHolder2 = $this->getAppSetting(Globals::SETTING_BANK_ACCOUNT_HOLDER_2);
        $this->bankAccountNumber2 = $this->getAppSetting(Globals::SETTING_BANK_ACCOUNT_NUMBER_2);
        $this->cityOfBank2 = $this->getAppSetting(Globals::SETTING_CITY_OF_BANK_2);
        $this->countryOfBank2 = $this->getAppSetting(Globals::SETTING_COUNTRY_OF_BANK_2);*/
        $this->pg = $this->getRequestParameter('pg', 'N');

        if ($this->getRequestParameter('fundAmount') != "") {
            $amount = $this->getRequestParameter('fundAmount');
            $walletType = $this->getRequestParameter('walletType');
            //$paymentReference = $this->generatePaymentReference();
            $dispAmount = $amount;
            $paymentMethod = $this->getRequestParameter('paymentMethod', 'LB');

            $mlmDistEpointPurchase = new MlmDistEpointPurchase();
            $mlmDistEpointPurchase->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $mlmDistEpointPurchase->setPaymentMethod($paymentMethod);
            $mlmDistEpointPurchase->setAmount($amount);
            $mlmDistEpointPurchase->setWalletType($walletType);
            $mlmDistEpointPurchase->setPaymentReference("");
            $mlmDistEpointPurchase->setTransactionType(Globals::PURCHASE_EPOINT_BANK_TRANSFER);
            $mlmDistEpointPurchase->setStatusCode(Globals::STATUS_PENDING);
            $mlmDistEpointPurchase->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlmDistEpointPurchase->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlmDistEpointPurchase->save();

            $mlmDistEpointPurchase->setPaymentReference($mlmDistEpointPurchase->getPurchaseId());
            $mlmDistEpointPurchase->save();

            $paymentReference = $mlmDistEpointPurchase->getPaymentReference();

            if ($paymentMethod == "GOZ") {
                $ver = "1.0";
                $merid = "100009";
                $orderid = $paymentReference;
                $amount = number_format($dispAmount, 2);
                $orderdate = date("Ymd");
                $curtype = "RMB";
                $paytype = "01";
                $lang = "GB";
                $returnurl = "http://localhost:6666/member/gozSuccessRedirect";
                $errorurl = "http://localhost:6666/member/gozErrorRedirect";
                $remark = $distDB->getDistributorCode();
                $enctype = "1";
                $channelid = $this->getRequestParameter('channelid', "CMB");

                $this->ver = $ver;
                $this->merid = $merid;
                $this->orderid = $orderid;
                $this->amount = $amount;
                $this->orderdate = $orderdate;
                $this->curtype = $curtype;
                $this->paytype = $paytype;
                $this->lang = $lang;
                $this->returnurl = $returnurl;
                $this->errorurl = $errorurl;
                $this->remark = $remark;
                $this->enctype = $enctype;
                $this->channelid = $channelid;

                $md5Key = "88496625849445331821427993934397583101845496550535688096140279054296113998693043340961948795056633136331268949200793818235742794";
                $orge = 'ver=' . $ver . '&merid=' . $merid . '&orderid=' . $orderid . '&amount=' . $amount . '&orderdate=' . $orderdate . '&curtype=' . $curtype . '&paytype=' . $paytype . '&lang=' . $lang . '&returnurl=' . $returnurl . '&errorurl=' . $errorurl . '&remark1=' . $remark . '&enctype=' . $enctype . '&notifytype=2&urltype=1&s2surl=' . $errorurl . '&goodsname=goods&channelid=' . $channelid;
                //echo  $orge;
                $this->SignMD5 = md5($orge . $md5Key);

                $mlmDistEpointPurchase->setPgMsg($orge);
                $mlmDistEpointPurchase->setPgBillNo($orderid);
                $mlmDistEpointPurchase->setPgRetEncodeType($channelid);
                $mlmDistEpointPurchase->setPgCurrencyType($curtype);
                $mlmDistEpointPurchase->setPgSignature($this->SignMD5);
                $mlmDistEpointPurchase->save();

                $this->setTemplate('fundsDepositGoz');
            } else {
                $this->setFlash('purchaseId', $mlmDistEpointPurchase->getPurchaseId());
                $this->setFlash('amount', $amount);
                $this->setFlash('paymentReference', $paymentReference);
                $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Your requests has been submitted, to complete the funding, please proceed to remit the payment to the account, with details as indicated below:"));
                return $this->redirect('/member/fundsDeposit');
            }
        }
    }

    public function executeMpRebate()
    {
        $this->cardMpReturns = $this->findCardMpReturnList($this->getUser()->getAttribute(Globals::SESSION_DISTID));

        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "MY_STATEMENT");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "MP_REBATE");
    }

    public function executeActiveMember()
    {
        $c = new Criteria();
        $c->add(MlmIbPeer::DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $c->add(MlmIbPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $mlmIb = MlmIbPeer::doSelectOne($c);

        if (!$mlmIb) {
            $this->setFlash('errorMsg', "Invalid Action.");
            return $this->redirect('/member/summary');
        }

        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "REGISTRATION");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "ACTIVE_MEMBER");

        $c = new Criteria();
        $c->add(MlmIbPeer::DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $c->add(MlmIbPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $mlmIb = MlmIbPeer::doSelectOne($c);

        if ($mlmIb) {
            $c = new Criteria();
            $c->add(MlmDistributorPeer::REGISTERED_IB, $mlmIb->getIbId());
            $c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_PENDING);
            $this->pendingDistributors = MlmDistributorPeer::doSelect($c);
        }
    }

    public function executePasswordSetting()
    {
        if ($this->getUser()->getAttribute(Globals::SESSION_SECURITY_PASSWORD_REQUIRED_PASSWORD_SETTING, false) == false) {
            return $this->redirect('/member/securityPasswordRequired?doAction=P');
        }
    }

    public function executeActiveMemberList()
    {
        $c = new Criteria();
        $c->add(MlmIbPeer::DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $c->add(MlmIbPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $mlmIb = MlmIbPeer::doSelectOne($c);

        if (!$mlmIb) {
            $this->setFlash('errorMsg', "Invalid Action.");
            return $this->redirect('/member/summary');
        }

        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "REGISTRATION");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "ACTIVE_MEMBER_LIST");
    }

    public function executeInactiveMemberList()
    {
        $c = new Criteria();
        $c->add(MlmIbPeer::DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $c->add(MlmIbPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $mlmIb = MlmIbPeer::doSelectOne($c);

        if (!$mlmIb) {
            $this->setFlash('errorMsg', "Invalid Action.");
            return $this->redirect('/member/summary');
        }

        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "REGISTRATION");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "INACTIVE_MEMBER");
    }

    public function executeSecurityPasswordRequired()
    {
        $doAction = $this->getRequestParameter('doAction', "VP");
        $this->doAction = $doAction;

        if ($this->getRequestParameter('transactionPassword')) {
            $c = new Criteria();
            $c->add(AppUserPeer::USER_ID, $this->getUser()->getAttribute(Globals::SESSION_USERID));
            $c->add(AppUserPeer::USERPASSWORD2, $this->getRequestParameter('transactionPassword'));
            $exist = AppUserPeer::doSelectOne($c);

            if (!$exist) {
                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Security password is not valid."));
                return $this->redirect('/member/securityPasswordRequired?doAction=' . $doAction);
            }

            if ($doAction == "VP") {
                $this->getUser()->setAttribute(Globals::SESSION_SECURITY_PASSWORD_REQUIRED_VIEW_PROFILE, true);
                return $this->redirect('/member/viewProfile');
            } else if ($doAction == "G") {
                $this->getUser()->setAttribute(Globals::SESSION_SECURITY_PASSWORD_REQUIRED_GENEALOGY, true);
                return $this->redirect('/member/sponsorTree');
            } else if ($doAction == "C") {
                $this->getUser()->setAttribute(Globals::SESSION_SECURITY_PASSWORD_REQUIRED_COMMISSION, true);
                return $this->redirect('/member/bonusDetails');
            } else if ($doAction == "W") {
                $this->getUser()->setAttribute(Globals::SESSION_SECURITY_PASSWORD_REQUIRED_WALLET, true);
                return $this->redirect('/member/mgLog');
            } else if ($doAction == "P") {
                $this->getUser()->setAttribute(Globals::SESSION_SECURITY_PASSWORD_REQUIRED_PASSWORD_SETTING, true);
                return $this->redirect('/member/passwordSetting');
            }
        }
    }

    public function executeTestSendReport()
    {
        //$this->sendDailyReport();

        $c = new Criteria();
        //$c->add(MlmDistributorPeer::TREE_STRUCTURE, "%|933|%");
        $c->add(MlmDistributorPeer::TREE_STRUCTURE, "%|989|%", Criteria::LIKE);
        $c->addAscendingOrderByColumn(MlmDistributorPeer::TREE_LEVEL);

        $mlmDistributors = MlmDistributorPeer::doSelect($c);

        foreach ($mlmDistributors as $mlmDistributor) {
            print_r($mlmDistributor->getDistributorId() . "<br>");
            $uplineDistDB = MlmDistributorPeer::retrieveByPK($mlmDistributor->getUplineDistId());

            $mlmDistributor->setTreeLevel($uplineDistDB->getTreeLevel() + 1);
            $mlmDistributor->setTreeStructure($uplineDistDB->getTreeStructure() . "|" . $mlmDistributor->getDistributorId() . "|");
            $mlmDistributor->save();
        }
        $c = new Criteria();
        $c->add(MlmDistributorPeer::TREE_STRUCTURE, "%|933|%", Criteria::LIKE);
        //        $c->add(MlmDistributorPeer::TREE_STRUCTURE, "%|989|%");
        $c->addAscendingOrderByColumn(MlmDistributorPeer::TREE_LEVEL);

        $mlmDistributors = MlmDistributorPeer::doSelect($c);

        foreach ($mlmDistributors as $mlmDistributor) {
            print_r($mlmDistributor->getDistributorId() . "<br>");
            $uplineDistDB = MlmDistributorPeer::retrieveByPK($mlmDistributor->getUplineDistId());

            $mlmDistributor->setTreeLevel($uplineDistDB->getTreeLevel() + 1);
            $mlmDistributor->setTreeStructure($uplineDistDB->getTreeStructure() . "|" . $mlmDistributor->getDistributorId() . "|");
            $mlmDistributor->save();
        }

        print_r("Done");
        return sfView::HEADER_ONLY;
    }

    public function executeDoCustomerEnquiry()
    {
        $contactNoEmail = $this->getRequestParameter('contactNoEmail');
        $title = $this->getRequestParameter('title');
        $message = $this->getRequestParameter('message');
        $transactionPassword = $this->getRequestParameter('transactionPassword');

        if ($this->getRequestParameter('transactionPassword') == "") {
            $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Security password is blank."));
            return $this->redirect('/member/customerEnquiry');
        }

        $appUser = AppUserPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_USERID));
        if (strtoupper($appUser->getUserPassword2()) <> strtoupper($this->getRequestParameter('transactionPassword'))) {
            $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Invalid Security password"));
            return $this->redirect('/member/customerEnquiry');
        }
        //var_dump($contactNoEmail);
        //var_dump($title);
        //var_dump($message);
        //var_dump($transactionPassword);

        $mlm_customer_enquiry = new MlmCustomerEnquiry();
        $mlm_customer_enquiry->setDistributorId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $mlm_customer_enquiry->setContactNo($contactNoEmail);
        $mlm_customer_enquiry->setTitle($title);
        $mlm_customer_enquiry->setAdminUpdated(Globals::FALSE);
        $mlm_customer_enquiry->setDistributorUpdated(Globals::TRUE);
        $mlm_customer_enquiry->setAdminRead(Globals::FALSE);
        $mlm_customer_enquiry->setDistributorRead(Globals::TRUE);
        $mlm_customer_enquiry->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_customer_enquiry->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));

        $mlm_customer_enquiry->save();

        $mlm_customer_enquiry_detail = new MlmCustomerEnquiryDetail();
        $mlm_customer_enquiry_detail->setCustomerEnquiryId($mlm_customer_enquiry->getEnquiryId());
        $mlm_customer_enquiry_detail->setMessage($message);
        $mlm_customer_enquiry_detail->setReplyFrom(Globals::ROLE_DISTRIBUTOR);
        $mlm_customer_enquiry_detail->setStatusCode(Globals::STATUS_ACTIVE);
        $mlm_customer_enquiry_detail->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_customer_enquiry_detail->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_customer_enquiry_detail->save();

        $message = "Member ID: " . $this->getUser()->getAttribute(Globals::SESSION_USERNAME) . "<br>Contact No: " . $contactNoEmail . "<br><br>Message: " . $message;

        $sendMailService = new SendMailService();
        $sendMailService->sendMail("support@sixstarclub.us", "support", "[Customer Enquiry]" . $title, $message);

        $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Your inquiry has been submitted."));
        return $this->redirect('/member/customerEnquiry');
    }

    public function executeDoCustomerEnquiryDetail()
    {
        $enquiryId = $this->getRequestParameter('enquiryId');
        $message = $this->getRequestParameter('message');
        $transactionPassword = $this->getRequestParameter('transactionPassword');

        if ($this->getRequestParameter('transactionPassword') == "") {
            $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Security password is blank."));
            return $this->redirect('/member/customerEnquiryDetail?enquiryId=' . $enquiryId);
        }

        $appUser = AppUserPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_USERID));
        if (strtoupper($appUser->getUserPassword2()) <> strtoupper($this->getRequestParameter('transactionPassword'))) {
            $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Invalid Security password"));
            return $this->redirect('/member/customerEnquiryDetail?enquiryId=' . $enquiryId);
        }

        $mlmCustomerEnquiry = MlmCustomerEnquiryPeer::retrieveByPK($enquiryId);
        $mlmCustomerEnquiry->setDistributorUpdated(Globals::TRUE);
        $mlmCustomerEnquiry->setAdminUpdated(Globals::TRUE);
        $mlmCustomerEnquiry->setAdminRead(Globals::FALSE);
        $mlmCustomerEnquiry->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));

        $mlmCustomerEnquiry->save();

        $mlm_customer_enquiry_detail = new MlmCustomerEnquiryDetail();
        $mlm_customer_enquiry_detail->setCustomerEnquiryId($mlmCustomerEnquiry->getEnquiryId());
        $mlm_customer_enquiry_detail->setMessage($message);
        $mlm_customer_enquiry_detail->setReplyFrom(Globals::ROLE_DISTRIBUTOR);
        $mlm_customer_enquiry_detail->setStatusCode(Globals::STATUS_ACTIVE);
        $mlm_customer_enquiry_detail->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_customer_enquiry_detail->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_customer_enquiry_detail->save();

        $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Your inquiry has been submitted."));
        return $this->redirect('/member/customerEnquiryDetail?enquiryId=' . $enquiryId);
    }

    public function executeCustomerEnquiry()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "CS_CENTER");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "CS_CENTER");

        $this->username = $this->getUser()->getAttribute(Globals::SESSION_USERNAME);
    }

    public function executeCustomerEnquiryDetail()
    {
        $enquiryId = $this->getRequestParameter('enquiryId');

        $mlmCustomerEnquiry = MlmCustomerEnquiryPeer::retrieveByPK($enquiryId);

        if (!$mlmCustomerEnquiry) {
            $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Invalid Action."));
            return $this->redirect('/member/customerEnquiry');
        }
        $mlmCustomerEnquiry->setDistributorRead(Globals::TRUE);
        $mlmCustomerEnquiry->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlmCustomerEnquiry->save();

        if ($mlmCustomerEnquiry->getDistributorId() != $this->getUser()->getAttribute(Globals::SESSION_DISTID)) {
            $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Invalid Action."));
            return $this->redirect('/member/customerEnquiry');
        }

        $c = new Criteria();
        $c->add(MlmCustomerEnquiryDetailPeer::CUSTOMER_ENQUIRY_ID, $enquiryId);
        $mlmCustomerEnquiryDetails = MlmCustomerEnquiryDetailPeer::doSelect($c);

        $this->mlmCustomerEnquiry = $mlmCustomerEnquiry;
        $this->mlmCustomerEnquiryDetails = $mlmCustomerEnquiryDetails;
    }

    public function executeVerifyActivePlacementDistId()
    {
        $sponsorId = $this->getRequestParameter('sponsorId');
        $placementDistId = $this->getRequestParameter('placementDistId');

        $distId = $this->getDistributorIdByCode($sponsorId);

        //$array = explode(',', Globals::STATUS_ACTIVE.",".Globals::STATUS_PENDING);
        $c = new Criteria();
        $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $placementDistId);
        $c->add(MlmDistributorPeer::PLACEMENT_TREE_STRUCTURE, "%" . $distId . "%", Criteria::LIKE);
        $c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $existUser = MlmDistributorPeer::doSelectOne($c);

        $arr = "";
        if ($existUser) {
            //if ($existUser->getDistributorId() <> $this->getUser()->getAttribute(Globals::SESSION_DISTID)) {
            $arr = array(
                'userId' => $existUser->getDistributorId(),
                'userName' => $existUser->getDistributorCode(),
                'fullname' => $existUser->getFullName(),
                'nickname' => $existUser->getNickname()
            );
            //}
        }

        echo json_encode($arr);
        return sfView::HEADER_ONLY;
    }

    public function executePurchasePackageViaTree()
    {
        $uplineDistCode = $this->getRequestParameter('distcode');
        $position = $this->getRequestParameter('position');

        $c = new Criteria();
        $c->add(MlmPackagePeer::PUBLIC_PURCHASE, 1);
        $packageDBs = MlmPackagePeer::doSelect($c);

        $this->systemCurrency = $this->getAppSetting(Globals::SETTING_SYSTEM_CURRENCY);
        $this->pointAvailable = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_EPOINT);
        $this->ecashAvailable = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);
        $this->packageDBs = $packageDBs;

        $this->uplineDistCode = $uplineDistCode;
        $this->position = $position;
    }

    public function executePurchasePackageViaTree2()
    {
        $this->uplineDistCode = $this->getRequestParameter('uplineDistCode');
        $this->position = $this->getRequestParameter('position');
        $this->systemCurrency = $this->getAppSetting(Globals::SETTING_SYSTEM_CURRENCY);
        //var_dump($this->getRequestParameter('uplineDistCode'));
        if ($this->getRequestParameter('pid') <> "") {
            $ledgerEPointBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_EPOINT);
            $ledgerECashBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);
            $selectedPackage = MlmPackagePeer::retrieveByPK($this->getRequestParameter('pid'));
            $this->forward404Unless($selectedPackage);

            $amountNeeded = $selectedPackage->getPrice();

            $epointPaid = $this->getRequestParameter('epointPaid');
            $ecashPaid = $this->getRequestParameter('ecashPaid');

            /*if ($selectedPackage->getPackageId() == Globals::MAX_PACKAGE_ID) {
                $amountNeeded = $this->getRequestParameter('specialPackagePrice');
            }*/

            $existDist = MlmDistributorPeer::retrieveByPK($this->getRequestParameter('sponsorId', $this->getUser()->getAttribute(Globals::SESSION_DISTID)));
            $this->forward404Unless($existDist);
            $this->sponsorId = $existDist->getDistributorCode();
            $this->sponsorName = $existDist->getFullName();

            if ($epointPaid > $ledgerEPointBalance) {
                $this->setFlash('errorMsg', "In-sufficient CP1 amount");
                return $this->redirect('/member/purchasePackageViaTree?distcode=' . $this->uplineDistCode . '&position=' . $this->position);
            }
            if ($ecashPaid > $ledgerECashBalance) {
                $this->setFlash('errorMsg', "In-sufficient CP2 amount");
                return $this->redirect('/member/purchasePackageViaTree?distcode=' . $this->uplineDistCode . '&position=' . $this->position);
            }
            $totalPaid = $epointPaid + $ecashPaid;
            if ($totalPaid < $amountNeeded) {
                $this->setFlash('errorMsg', "In-sufficient fund to purchase package");
                return $this->redirect('/member/purchasePackageViaTree?distcode=' . $this->uplineDistCode . '&position=' . $this->position);
            }
            if ($totalPaid > $amountNeeded) {
                $this->setFlash('errorMsg', "Amount Paid is not tally with package price");
                return $this->redirect('/member/purchasePackageViaTree?distcode=' . $this->uplineDistCode . '&position=' . $this->position);
            }
            if ($amountNeeded > $ledgerEPointBalance) {
                $this->setFlash('errorMsg', "In-sufficient CP1 amount");
                return $this->redirect('/member/purchasePackageViaTree?distcode=' . $this->uplineDistCode . '&position=' . $this->position);
            }

            $this->selectedPackage = $selectedPackage;
            $this->epointPaid = $epointPaid;
            $this->ecashPaid = $ecashPaid;
            $this->amountNeeded = $amountNeeded;
            $this->productCode = $this->getRequestParameter('productCode');

            $c = new Criteria();
            $c->add(MlmDistributorPeer::UPLINE_DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
            $totalNetworks = MlmDistributorPeer::doCount($c);

            $this->totalNetworks = $totalNetworks;
        } else {
            return $this->redirect('/member/placementTree');
        }
    }

    public function executeUpgradePackageViaTree()
    {
        $distCode = $this->getRequestParameter('distcode');
        $c = new Criteria();
        $c->add(MlmPackagePeer::PUBLIC_PURCHASE, 1);
        $c->addAscendingOrderByColumn(MlmPackagePeer::PRICE);
        $packageDBs = MlmPackagePeer::doSelect($c);

        /*$c = new Criteria();
        $c->addDescendingOrderByColumn(MlmPackagePeer::PRICE);
        $highestPackageDB = MlmPackagePeer::doSelectOne($c);*/

        $c = new Criteria();
        $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $distCode);
        $distDB = MlmDistributorPeer::doSelectOne($c);
        $this->forward404Unless($distDB);

        $distPackage = MlmPackagePeer::retrieveByPK($distDB->getRankId());

        $this->systemCurrency = $this->getAppSetting(Globals::SETTING_SYSTEM_CURRENCY);
        $this->pointAvailable = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_EPOINT);
        $this->packageDBs = $packageDBs;
        $this->distPackage = $distPackage;
        $this->distDB = $distDB;
        //$this->highestPackageDB = $highestPackageDB;
        $this->distCode = $distCode;
    }

    public function executeUnderMaintenance()
    {
    }

    public function executeExchange()
    {
    }

    public function executeDailyFxGuide()
    {
    }

    public function executeFundManagementReport()
    {
    }

    public function executeForgetPassword()
    {
        if ($this->getRequestParameter('email') && $this->getRequestParameter('username')) {
            $email = $this->getRequestParameter('email');
            $username = $this->getRequestParameter('username');

            $this->email = $email;
            $this->username = $username;

            $c = new Criteria();
            $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $username);
            $c->add(MlmDistributorPeer::EMAIL, $email);
            $c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
            $existDistributor = MlmDistributorPeer::doSelectOne($c);

            if ($existDistributor) {
                $c = new Criteria();
                $c->add(AppUserPeer::USERNAME, $username);
                $c->add(AppUserPeer::USER_ROLE, Globals::ROLE_DISTRIBUTOR);
                $c->add(AppUserPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
                $existUser = AppUserPeer::doSelectOne($c);

                if ($existUser) {
                    /****************************/
                    /*****  Send email **********/
                    /****************************/
                    $password = $existUser->getUserpassword();

                    $subject = $this->getContext()->getI18N()->__("Password requested for sixstarclub.us", null, 'email');
                    $body = $this->getContext()->getI18N()->__("Dear %1%", array('%1%' => $existDistributor->getNickname()), 'email') . ",<p><p>
                    <p>" . $this->getContext()->getI18N()->__("Your login password for sixstarclub.us. ", null, 'email') . "</p>
                    <p><b>" . $this->getContext()->getI18N()->__("Login ID", null) . ": " . $username . "</b>
                    <p><b>" . $this->getContext()->getI18N()->__("Password", null) . ": " . $password . "</b>";

                    $sendMailService = new SendMailService();
                    $sendMailService->sendForgetPassword($existDistributor, $subject, $body);

                    $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Password already sent to your email account. Please check your inbox."));
                } else {
                    $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Email is not matching to your username."));
                }
            } else {
                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Email is not matching to your username."));
            }
            return $this->redirect('/member/forgetPassword');
        }
    }

    public function executeInitData()
    {
        var_dump($_SERVER['HTTP_HOST']);
        print_r("Done");
        return sfView::HEADER_ONLY;
    }

    public function executePrintBankInformation()
    {
        $purchaseId = $this->getRequestParameter('p');

        $c = new Criteria();
        $c->add(MlmDistEpointPurchasePeer::PURCHASE_ID, $purchaseId);
        $c->add(MlmDistEpointPurchasePeer::DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $distPackagePurchase = MlmDistEpointPurchasePeer::doSelectOne($c);

        $bankId = 1;
        if ($distPackagePurchase) {
            $this->purchaseId = $distPackagePurchase->getPurchaseId();
            $this->amount = $distPackagePurchase->getAmount();
            $this->paymentReference = $distPackagePurchase->getPaymentReference();
            $bankId = $distPackagePurchase->getBankId();
        }
        $this->tradingCurrencyOnMT4 = "USD";
        if ($bankId == 2) {
            $this->bankName = $this->getAppSetting(Globals::SETTING_BANK_NAME_2);
            $this->bankSwiftCode = $this->getAppSetting(Globals::SETTING_BANK_SWIFT_CODE_2);
            $this->iban = $this->getAppSetting(Globals::SETTING_IBAN_2);
            $this->bankAccountHolder = $this->getAppSetting(Globals::SETTING_BANK_ACCOUNT_HOLDER_2);
            $this->bankAccountNumber = $this->getAppSetting(Globals::SETTING_BANK_ACCOUNT_NUMBER_2);
            $this->cityOfBank = $this->getAppSetting(Globals::SETTING_CITY_OF_BANK_2);
            $this->countryOfBank = $this->getAppSetting(Globals::SETTING_COUNTRY_OF_BANK_2);
        } else {
            $this->bankName = $this->getAppSetting(Globals::SETTING_BANK_NAME);
            $this->bankSwiftCode = $this->getAppSetting(Globals::SETTING_BANK_SWIFT_CODE);
            $this->iban = $this->getAppSetting(Globals::SETTING_IBAN);
            $this->bankAccountHolder = $this->getAppSetting(Globals::SETTING_BANK_ACCOUNT_HOLDER);
            $this->bankAccountNumber = $this->getAppSetting(Globals::SETTING_BANK_ACCOUNT_NUMBER);
            $this->cityOfBank = $this->getAppSetting(Globals::SETTING_CITY_OF_BANK);
            $this->countryOfBank = $this->getAppSetting(Globals::SETTING_COUNTRY_OF_BANK);
        }

        $this->systemCurrency = $this->getAppSetting(Globals::SETTING_SYSTEM_CURRENCY);
    }

    public function executeIndex()
    {
        return $this->redirect('/member/summary');
    }

    public function executePackagePurchaseViaBankTransfer()
    {
        $c = new Criteria();
        $c->addDescendingOrderByColumn(MlmPackagePeer::PRICE);
        $packages = MlmPackagePeer::doSelect($c);

        $distDB = MlmDistributorPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $this->forward404Unless($distDB);
        $this->packages = $packages;
        $this->distDB = $distDB;

        if ($this->getRequestParameter('packageTypeId') != "" && $this->getRequest()->getFileName('bankSlip') != '') {
            $packageDB = MlmPackagePeer::retrieveByPk($this->getRequestParameter('packageTypeId'));
            $this->forward404Unless($packageDB);

            $uploadedFilename = $this->getRequest()->getFileName('bankSlip');
            $ext = explode(".", $this->getRequest()->getFileName('bankSlip'));
            $extensionName = $ext[count($ext) - 1];

            $filename = date("Ymd") . "_" . $distDB->getDistributorCode() . "_" . rand(1000, 9999) . "." . $extensionName;
            $this->getRequest()->moveFile('bankSlip', sfConfig::get('sf_upload_dir') . '/bankslip/' . $filename);

            $mlmDistPackagePurchase = new MlmDistPackagePurchase();
            $mlmDistPackagePurchase->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $mlmDistPackagePurchase->setRankId($packageDB->getPackageId());
            $mlmDistPackagePurchase->setRankCode($packageDB->getPackageName());
            //$mlmDistPackagePurchase->setInitRankId($packageDB->getPackageId());
            //$mlmDistPackagePurchase->setInitRankCode($packageDB->getPackageName());
            $mlmDistPackagePurchase->setAmount($packageDB->getPrice());
            $mlmDistPackagePurchase->setTransactionType(Globals::PURCHASE_PACKAGE_BANK_TRANSFER);
            $mlmDistPackagePurchase->setImageSrc($_SERVER['HTTP_HOST'] . "/uploads/bankslip/" . $filename);
            $mlmDistPackagePurchase->setStatusCode(Globals::STATUS_PENDING);
            //$mlmDistPackagePurchase->setRemarks($this->getRequestParameter('remarks'));
            $mlmDistPackagePurchase->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlmDistPackagePurchase->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));

            $mlmDistPackagePurchase->save();

            $distDB->setStatusCode(Globals::STATUS_PAYMENT_PENDING);
            $distDB->save();
            $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Your requests has been submitted."));
            return $this->redirect('/member/summary');
        }
    }

    public function executeDoUploadFile()
    {
        if ($this->getRequest()->getFileName('bankPassBook') != '') {
            $distDB = MlmDistributorPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $this->forward404Unless($distDB);

            $uploadedFilename = $this->getRequest()->getFileName('bankPassBook');
            $ext = explode(".", $this->getRequest()->getFileName('bankPassBook'));
            $extensionName = $ext[count($ext) - 1];

            $filename = "bankPassBook_" . date("Ymd") . "_" . $distDB->getDistributorCode() . "_" . rand(1000, 9999) . "." . $extensionName;
            $this->getRequest()->moveFile('bankPassBook', sfConfig::get('sf_upload_dir') . '/bank_pass_book/' . $filename);

            $distDB->setFileBankPassBook($filename);
            $distDB->save();

            $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Upload successful."));
        }
        if ($this->getRequest()->getFileName('proofOfResidence') != '') {
            $distDB = MlmDistributorPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $this->forward404Unless($distDB);

            $uploadedFilename = $this->getRequest()->getFileName('proofOfResidence');
            $ext = explode(".", $this->getRequest()->getFileName('proofOfResidence'));
            $extensionName = $ext[count($ext) - 1];

            $filename = "proofOfResidence_" . date("Ymd") . "_" . $distDB->getDistributorCode() . "_" . rand(1000, 9999) . "." . $extensionName;
            $this->getRequest()->moveFile('proofOfResidence', sfConfig::get('sf_upload_dir') . '/proof_of_residence/' . $filename);

            $distDB->setFileProofOfResidence($filename);
            $distDB->save();

            $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Upload successful."));
        }
        if ($this->getRequest()->getFileName('nric') != '') {
            $distDB = MlmDistributorPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $this->forward404Unless($distDB);

            $uploadedFilename = $this->getRequest()->getFileName('nric');
            $ext = explode(".", $this->getRequest()->getFileName('nric'));
            $extensionName = $ext[count($ext) - 1];

            $filename = "nric_" . date("Ymd") . "_" . $distDB->getDistributorCode() . "_" . rand(1000, 9999) . "." . $extensionName;
            $this->getRequest()->moveFile('nric', sfConfig::get('sf_upload_dir') . '/nric/' . $filename);

            $distDB->setFileNric($filename);
            $distDB->save();

            $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Upload successful."));
        }
        return $this->redirect('/member/viewProfile');
    }

    public function executeUploadBankReceipt()
    {
        if ($this->getRequestParameter('purchaseId') != "" && $this->getRequest()->getFileName('bankSlip') != '') {
            $distDB = MlmDistributorPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $this->forward404Unless($distDB);
            $this->distDB = $distDB;

            $uploadedFilename = $this->getRequest()->getFileName('bankSlip');
            $ext = explode(".", $this->getRequest()->getFileName('bankSlip'));
            $extensionName = $ext[count($ext) - 1];

            $filename = date("Ymd") . "_" . $distDB->getDistributorCode() . "_" . rand(1000, 9999) . "." . $extensionName;
            $this->getRequest()->moveFile('bankSlip', sfConfig::get('sf_upload_dir') . '/bankslip/' . $filename);

            $mlmDistEpointPurchase = MlmDistEpointPurchasePeer::retrieveByPK($this->getRequestParameter('purchaseId'));
            $mlmDistEpointPurchase->setImageSrc("http://" . $_SERVER['HTTP_HOST'] . "/uploads/bankslip/" . $filename);
            $mlmDistEpointPurchase->setBankId($this->getRequestParameter('bankId'));
            $mlmDistEpointPurchase->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));

            $mlmDistEpointPurchase->save();

            $this->setFlash('banksuccessMsg', $this->getContext()->getI18N()->__("Bank receipt upload successful."));
            return $this->redirect('/member/cp1Purchase');
        }
    }

    public function executeUpdateTermCondition()
    {
        $mlm_distributor = MlmDistributorPeer::retrieveByPK($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $this->forward404Unless($mlm_distributor);

        $mlm_distributor->setTermCondition(Globals::YES);
        $mlm_distributor->save();
        return $this->redirect('/member/summary');
    }

    public function executeRegister()
    {
        $char = strtoupper(substr(str_shuffle('abcdefghjkmnpqrstuvwxyz'), 0, 2));
        // Concatenate the random string onto the random numbers
        // The font 'Anorexia' doesn't have a character for '8', so the numbers will only go up to 7
        // '0' is left out to avoid confusion with 'O'

        $str = rand(1, 7) . rand(1, 7) . $char;
        $this->getUser()->setAttribute(Globals::SYSTEM_CAPTCHA_ID, $str);
    }

    public function executeMemberRegistration()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "REGISTRATION");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "REGISTRATION");

        /*$c = new Criteria();
        $c->add(MlmPackagePeer::PUBLIC_PURCHASE, 1);
        $packageDBs = MlmPackagePeer::doSelect($c);

        $this->systemCurrency = $this->getAppSetting(Globals::SETTING_SYSTEM_CURRENCY);
        $this->pointAvailable = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_EPOINT);
        $this->ecashAvailable = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);
        $this->packageDBs = $packageDBs;*/
    }

    public function executeMemberRegistration2()
    {
        //if ($this->getRequestParameter('transactionPassword') <> "" && $this->getRequestParameter('pid') <> "") {
        $this->systemCurrency = $this->getAppSetting(Globals::SETTING_SYSTEM_CURRENCY);
        if ($this->getRequestParameter('pid') <> "") {
            /*$tbl_user = AppUserPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_USERID));
            if ($tbl_user->getUserpassword2() <> $this->getRequestParameter('transactionPassword')) {
                $this->setFlash('errorMsg', "Invalid Security password");
                return $this->redirect('/member/memberRegistration');
            }*/

            $ledgerEPointBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_EPOINT);
            $ledgerECashBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);

            $selectedPackage = MlmPackagePeer::retrieveByPK($this->getRequestParameter('pid'));
            $this->forward404Unless($selectedPackage);

            $amountNeeded = $selectedPackage->getPrice();

            $epointPaid = $this->getRequestParameter('epointPaid');
            $ecashPaid = $this->getRequestParameter('ecashPaid');
            $walletType = $this->getRequestParameter('walletType', 'ECASH');

            $this->walletType = $walletType;
            /*if ($selectedPackage->getPackageId() == Globals::MAX_PACKAGE_ID) {
                $amountNeeded = $this->getRequestParameter('specialPackagePrice');
            }*/

            if ($epointPaid > $ledgerEPointBalance) {
                $this->setFlash('errorMsg', "In-sufficient CP1 amount");
                return $this->redirect('/member/memberRegistration');
            }
            if ($ecashPaid > $ledgerECashBalance) {
                $this->setFlash('errorMsg', "In-sufficient CP2 amount");
                return $this->redirect('/member/memberRegistration');
            }
            $totalPaid = $epointPaid + $ecashPaid;
            if ($totalPaid < $amountNeeded) {
                $this->setFlash('errorMsg', "In-sufficient fund to purchase package");
                return $this->redirect('/member/memberRegistration');
            }
            if ($totalPaid > $amountNeeded) {
                $this->setFlash('errorMsg', "Amount Paid is not tally with package price");
                return $this->redirect('/member/memberRegistration');
            }
            $this->selectedPackage = $selectedPackage;
            $this->epointPaid = $epointPaid;
            $this->ecashPaid = $ecashPaid;
            $this->amountNeeded = $amountNeeded;
            $this->productCode = $this->getRequestParameter('productCode');

            $c = new Criteria();
            $c->add(MlmDistributorPeer::UPLINE_DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
            $totalNetworks = MlmDistributorPeer::doCount($c);

            $this->totalNetworks = $totalNetworks;
        } else {
            return $this->redirect('/member/memberRegistration');
        }
    }

    public function executeRegisterInfo()
    {
        if (!$this->getUser()->hasAttribute(Globals::SESSION_USERNAME)) {
            return $this->redirect('/member/register');
        }
    }

    public function executeUpdateProfile()
    {
        $mlm_distributor = MlmDistributorPeer::retrieveByPK($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $this->forward404Unless($mlm_distributor);

        //$mlm_distributor->setNickname($this->getRequestParameter('nickName'));
        //$mlm_distributor->setFullName($this->getRequestParameter('fullname'));
        $mlm_distributor->setIc($this->getRequestParameter('ic'));
        if ($this->getRequestParameter('country') == 'China') {
            $mlm_distributor->setCountry('China (PRC)');
        } else {
            $mlm_distributor->setCountry($this->getRequestParameter('country'));
        }
        $mlm_distributor->setAddress($this->getRequestParameter('address'));
        $mlm_distributor->setAddress2($this->getRequestParameter('address2'));
        $mlm_distributor->setCity($this->getRequestParameter('city'));
        $mlm_distributor->setState($this->getRequestParameter('state'));
        $mlm_distributor->setPostcode($this->getRequestParameter('zip'));
        $mlm_distributor->setEmail($this->getRequestParameter('email'));
        $mlm_distributor->setAlternateEmail($this->getRequestParameter('alt_email'));
        $mlm_distributor->setContact($this->getRequestParameter('contactNumber'));
        $mlm_distributor->setGender($this->getRequestParameter('gender'));
        if ($this->getRequestParameter('dob')) {
            list($d, $m, $y) = sfI18N::getDateForCulture($this->getRequestParameter('dob'), $this->getUser()->getCulture());
            $mlm_distributor->setDob("$y-$m-$d");
        }
        //$mlm_distributor->setBankName($this->getRequestParameter('bankName'));
        //$mlm_distributor->setBankAccNo($this->getRequestParameter('bankNo'));
        //$mlm_distributor->setBankHolderName($this->getRequestParameter('bankHolder'));
        $mlm_distributor->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_distributor->save();

        $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Profile update successfully"));

        return $this->redirect('/member/viewProfile');
    }

    public function executeUpdateBankInformation()
    {
        $mlm_distributor = MlmDistributorPeer::retrieveByPK($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $this->forward404Unless($mlm_distributor);

        $mlm_distributor->setBankName($this->getRequestParameter('bankName'));
        $mlm_distributor->setBankAccNo($this->getRequestParameter('bankAccNo'));
        $mlm_distributor->setBankHolderName($this->getRequestParameter('bankHolderName'));
        $mlm_distributor->setBankSwiftCode($this->getRequestParameter('bankSwiftCode'));
        $mlm_distributor->setVisaDebitCard($this->getRequestParameter('visaDebitCard'));
        $mlm_distributor->setBankState($this->getRequestParameter('bankState'));
        $mlm_distributor->setBankBranch($this->getRequestParameter('bankBranch'));
        $mlm_distributor->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_distributor->save();

        $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Bank Account Information update successfully"));

        //return $this->redirect('/member/viewBankInformation');
        return $this->redirect('/member/viewProfile');
    }

    public function executeDoRegister()
    {
        require_once('recaptchalib.php');
        $privatekey = "6LfhJtYSAAAAALocUxn6PpgfoWCFjRquNFOSRFdb";
        $resp = recaptcha_check_answer($privatekey,
                                       $_SERVER["REMOTE_ADDR"],
                                       $_POST["recaptcha_challenge_field"],
                                       $_POST["recaptcha_response_field"]);

        if (!$resp->is_valid) {
            $this->setFlash('errorMsg', "The CAPTCHA wasn't entered correctly. Go back and try it again.");
            return $this->redirect('home/login');
        }

        //$fcode = $this->generateFcode($this->getRequestParameter('country'));
        $fcode = $this->getRequestParameter('userName');
        $password = $this->getRequestParameter('userpassword');

        $c = new Criteria();
        $c->add(AppUserPeer::USERNAME, $fcode);
        $exist = AppUserPeer::doSelectOne($c);
        //$this->forward404Unless(!$exist);
        $parentId = $this->getDistributorIdByCode($this->getRequestParameter('sponsorId'));
        $this->forward404Unless($parentId <> 0);

        //******************* upline distributor ID
        $uplineDistDB = $this->getDistributorInformation($this->getRequestParameter('sponsorId'));
        $this->forward404Unless($uplineDistDB);

        $treeStructure = $uplineDistDB->getTreeStructure() . "|" . $fcode . "|";
        $treeLevel = $uplineDistDB->getTreeLevel() + 1;

        $app_user = new AppUser();
        $app_user->setUsername($fcode);
        $app_user->setKeepPassword($password);
        $app_user->setUserpassword($password);
        $app_user->setKeepPassword2($password);
        $app_user->setUserpassword2($password);
        $app_user->setUserRole(Globals::ROLE_DISTRIBUTOR);
        $app_user->setStatusCode(Globals::STATUS_PENDING);
        $app_user->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $app_user->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $app_user->save();

        // ****************************
        $mlm_distributor = new MlmDistributor();
        $mlm_distributor->setDistributorCode($fcode);
        $mlm_distributor->setUserId($app_user->getUserId());
        $mlm_distributor->setStatusCode(Globals::STATUS_PENDING);
        $mlm_distributor->setFullName($this->getRequestParameter('fullname'));
        $mlm_distributor->setNickname($fcode);
        $mlm_distributor->setIc($this->getRequestParameter('ic'));
        if ($this->getRequestParameter('country') == 'China') {
            $mlm_distributor->setCountry('China (PRC)');
        } else {
            $mlm_distributor->setCountry($this->getRequestParameter('country'));
        }
        $mlm_distributor->setAddress($this->getRequestParameter('address'));
        $mlm_distributor->setAddress2($this->getRequestParameter('address2'));
        $mlm_distributor->setCity($this->getRequestParameter('city'));
        $mlm_distributor->setState($this->getRequestParameter('state'));
        $mlm_distributor->setPostcode($this->getRequestParameter('zip'));
        $mlm_distributor->setEmail($this->getRequestParameter('email'));
        $mlm_distributor->setAlternateEmail($this->getRequestParameter('alt_email'));
        $mlm_distributor->setContact($this->getRequestParameter('contactNumber'));
        $mlm_distributor->setGender($this->getRequestParameter('gender'));
        if ($this->getRequestParameter('dob')) {
            list($d, $m, $y) = sfI18N::getDateForCulture($this->getRequestParameter('dob'), $this->getUser()->getCulture());
            $mlm_distributor->setDob("$y-$m-$d");
        }
        $mlm_distributor->setBankName($this->getRequestParameter('bankName'));
        $mlm_distributor->setBankAccNo($this->getRequestParameter('bankAccountNo'));
        $mlm_distributor->setBankHolderName($this->getRequestParameter('bankHolderName'));

        $mlm_distributor->setTreeLevel($treeLevel);
        $mlm_distributor->setTreeStructure($treeStructure);
        $mlm_distributor->setUplineDistId($uplineDistDB->getDistributorId());
        $mlm_distributor->setUplineDistCode($uplineDistDB->getDistributorCode());

        $mlm_distributor->setLeverage($this->getRequestParameter('leverage'));
        $mlm_distributor->setSpread($this->getRequestParameter('spread'));
        $mlm_distributor->setDepositCurrency($this->getRequestParameter('deposit_currency'));
        $mlm_distributor->setDepositAmount($this->getRequestParameter('deposit_amount'));
        $mlm_distributor->setSignName($this->getRequestParameter('sign_name'));
        $mlm_distributor->setSignDate(date("Y/m/d h:i:s A"));
        $mlm_distributor->setTermCondition($this->getRequestParameter('term_condition'));

        if ($this->getRequestParameter('productCode') == "fxgold") {
            $mlm_distributor->setProductMte("Y");
        }
        if ($this->getRequestParameter('productCode') == "mte") {
            $mlm_distributor->setProductFxgold("Y");
        }

        $mlm_distributor->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_distributor->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_distributor->save();

        $this->getUser()->setAttribute(Globals::SESSION_USERNAME, $fcode);

        /****************************/
        /*****  Send email **********/
        /****************************/
        /*error_reporting(E_STRICT);

        date_default_timezone_set(date_default_timezone_get());

        include_once('class.phpmailer.php');

        $subject = $this->getContext()->getI18N()->__("Vital Universe Group Registration email notification", null, 'email');
        $body = $this->getContext()->getI18N()->__("Dear %1%", array('%1%' => $mlm_distributor->getNickname()), 'email') . ",<p><p>

        <p>" . $this->getContext()->getI18N()->__("Your registration request has been successfully sent to Vital Universe Group", null, 'email') . "</p>
        <p><b>" . $this->getContext()->getI18N()->__("Trader ID", null) . ": " . $fcode . "</b>
        <p><b>" . $this->getContext()->getI18N()->__("Password", null) . ": " . $password . "</b>";

        $mail = new PHPMailer();
        $mail->IsMail(); // telling the class to use SMTP
        $mail->Host = Mails::EMAIL_HOST; // SMTP server
        $mail->Sender = Mails::EMAIL_FROM_NOREPLY;
        $mail->From = Mails::EMAIL_FROM_NOREPLY;
        $mail->FromName = Mails::EMAIL_FROM_NOREPLY_NAME;
        $mail->Subject = $subject;
        $mail->CharSet="utf-8";

        $text_body = $body;

        $mail->Body = $body;
        $mail->AltBody = $text_body;
        $mail->AddAddress($mlm_distributor->getEmail(), $mlm_distributor->getNickname());
        $mail->AddBCC("r9projecthost@gmail.com", "jason");

        if (!$mail->Send()) {
            echo $mail->ErrorInfo;
        }*/
        return $this->redirect('/member/registerInfo');
    }

    public function executeDoMemberRegistration()
    {
        if ($this->getRequestParameter('country') == "") {
            $this->setFlash('errorMsg', "Country is mandatory");
            return $this->redirect('/member/memberRegistration');
        }
        $fcode = $this->generateFcode($this->getRequestParameter('country'));
        $password = $this->getRequestParameter('userpassword');
        $password2 = $this->getRequestParameter('securityPassword');

        $con = Propel::getConnection(MlmDistributorPeer::DATABASE_NAME);
        try {
            $con->begin();
            //******************* upline distributor ID
            $c = new Criteria();
            $c->add(MlmIbPeer::IB_CODE, $this->getRequestParameter('reg_ib'));
            $c->add(MlmIbPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
            $existMlmIb = MlmIbPeer::doSelectOne($c);

            if (!$existMlmIb) {
                $this->setFlash('errorMsg', "Invalid Dealer ID");
                return $this->redirect('/member/memberRegistration');
            }

            $c = new Criteria();
            $c->add(MlmIbPeer::CLUB_CODE, $this->getRequestParameter('club_code'));
            $c->add(MlmIbPeer::IS_CLUB, "Y");
            $c->add(MlmIbPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
            $existMlmClub = MlmIbPeer::doSelectOne($c);

            if (!$existMlmClub) {
                $this->setFlash('errorMsg', "Invalid Club ID");
                return $this->redirect('/member/memberRegistration');
            }

            $uplineDistCode = $this->getRequestParameter('sponsorId');

            $c = new Criteria();
            $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $uplineDistCode);
            $c->add(MlmDistributorPeer::PLACEMENT_TREE_STRUCTURE, "%|" . $this->getUser()->getAttribute(Globals::SESSION_DISTID) . "|%", Criteria::LIKE);
            $c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
            $uplineDistDB = MlmDistributorPeer::doSelectOne($c);

            if (!$uplineDistDB) {
                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Invalid Referrer ID."));
                return $this->redirect('/member/memberRegistration');
            }

            $uplineDistId = $uplineDistDB->getDistributorId();
            $treeLevel = $uplineDistDB->getTreeLevel() + 1;

            $app_user = new AppUser();
            $app_user->setUsername($fcode);
            $app_user->setKeepPassword($password);
            $app_user->setUserpassword($password);
            $app_user->setKeepPassword2($password2);
            $app_user->setUserpassword2($password2);
            $app_user->setUserRole(Globals::ROLE_DISTRIBUTOR);
            $app_user->setStatusCode(Globals::STATUS_ACTIVE);
            $app_user->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $app_user->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $app_user->save();

            // ****************************

            $mlm_distributor = new MlmDistributor();
            $mlm_distributor->setDistributorCode($fcode);
            $mlm_distributor->setUserId($app_user->getUserId());
            $mlm_distributor->setStatusCode(Globals::STATUS_ACTIVE);
            $mlm_distributor->setFullName($this->getRequestParameter('fullname'));
            $mlm_distributor->setNickname($this->getRequestParameter('nickName'));
            $mlm_distributor->setIc($this->getRequestParameter('ic'));
            if ($this->getRequestParameter('country') == 'China') {
                $mlm_distributor->setCountry('China (PRC)');
            } else {
                $mlm_distributor->setCountry($this->getRequestParameter('country'));
            }
            $mlm_distributor->setAddress($this->getRequestParameter('address'));
            $mlm_distributor->setAddress2($this->getRequestParameter('address2'));
            $mlm_distributor->setCity($this->getRequestParameter('city'));
            $mlm_distributor->setState($this->getRequestParameter('state'));
            $mlm_distributor->setPostcode($this->getRequestParameter('zip'));
            $mlm_distributor->setEmail($this->getRequestParameter('email'));
            $mlm_distributor->setAlternateEmail($this->getRequestParameter('alt_email'));
            $mlm_distributor->setContact($this->getRequestParameter('contactNumber'));
            $mlm_distributor->setRegisteredIb($existMlmIb->getIbId());
            $mlm_distributor->setRegisteredClub($existMlmClub->getIbId());
            /*$mlm_distributor->setGender($this->getRequestParameter('gender'));
            if ($this->getRequestParameter('dob')) {
                list($d, $m, $y) = sfI18N::getDateForCulture($this->getRequestParameter('dob'), $this->getUser()->getCulture());
                $mlm_distributor->setDob("$y-$m-$d");
            }*/
            $mlm_distributor->setBankName($this->getRequestParameter('bankName'));
            $mlm_distributor->setBankAccNo($this->getRequestParameter('bankAccountNo'));
            $mlm_distributor->setBankHolderName($this->getRequestParameter('bankHolderName'));
            $mlm_distributor->setBankBranch($this->getRequestParameter('bankBranchName'));
            $mlm_distributor->setBankState($this->getRequestParameter('bankAddress'));
            $mlm_distributor->setBankSwiftCode($this->getRequestParameter('bankSwiftCode'));

            $mlm_distributor->setTreeLevel($treeLevel);
            //$mlm_distributor->setTreeStructure($treeStructure);
            $mlm_distributor->setUplineDistId($uplineDistDB->getDistributorId());
            $mlm_distributor->setUplineDistCode($uplineDistDB->getDistributorCode());

            //$mlm_distributor->setLeverage($this->getRequestParameter('leverage'));
            //$mlm_distributor->setSpread($this->getRequestParameter('spread'));
            //$mlm_distributor->setDepositCurrency($this->getRequestParameter('deposit_currency'));
            //$mlm_distributor->setDepositAmount($this->getRequestParameter('deposit_amount'));
            //$mlm_distributor->setSignName($this->getRequestParameter('sign_name'));
            //$mlm_distributor->setSignDate(date("Y/m/d h:i:s A"));
            //$mlm_distributor->setTermCondition($this->getRequestParameter('term_condition'));

            //$mlm_distributor->setRankId($packageDB->getPackageId());
            //$mlm_distributor->setRankCode($packageDB->getPackageName());
            //$mlm_distributor->setInitRankId($packageDB->getPackageId());
            //$mlm_distributor->setInitRankCode($packageDB->getPackageName());
            //$mlm_distributor->setStatusCode(Globals::STATUS_PENDING);
            /*if ($this->getUser()->getAttribute(Globals::SESSION_MASTER_LOGIN) == Globals::TRUE && $this->getUser()->getAttribute(Globals::SESSION_DISTID) == Globals::LOAN_ACCOUNT_CREATOR_DIST_ID) {
                $mlm_distributor->setPackagePurchaseFlag("N");
                $mlm_distributor->setRemark("loan account");
                $mlm_distributor->setLoanAccount("Y");
            } else {
                $mlm_distributor->setPackagePurchaseFlag("Y");
            }*/
            $mlm_distributor->setActiveDatetime(date("Y/m/d h:i:s A"));
            $mlm_distributor->setActivatedBy($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $mlm_distributor->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_distributor->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_distributor->save();

            $treeStructure = $uplineDistDB->getTreeStructure() . "|" . $mlm_distributor->getDistributorId() . "|";
            $mlm_distributor->setTreeStructure($treeStructure);
            $mlm_distributor->save();

            // Activate Member
            $mpBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_MP);

            if ($mpBalance < Globals::BASIC_MEMBER_CHARGE) {
                $this->setFlash('errorMsg', "In-sufficient MP");
                return $this->redirect('/member/memberRegistration');
            }

            $uplineDist = MlmDistributorPeer::retrieveByPK($mlm_distributor->getUplineDistId());

            if ($uplineDist) {
                $mlm_account_ledger = new MlmAccountLedger();
                $mlm_account_ledger->setDistId($uplineDist->getDistributorId());
                $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_MP);
                $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_REGISTER);
                $mlm_account_ledger->setRemark("DRB FOR MEMBER SIGN UP (" . $mlm_distributor->getDistributorCode() . ")");
                $mlm_account_ledger->setCredit(0);
                $mlm_account_ledger->setDebit(Globals::BASIC_MEMBER_CHARGE);
                $mlm_account_ledger->setBalance($mpBalance - Globals::BASIC_MEMBER_CHARGE);
                $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_account_ledger->save();

                $uplineCommission = Globals::BASIC_MEMBER_CHARGE * 0.3;

                $drbCommissionBalance = $this->getCommissionBalance($uplineDist->getDistributorId(), Globals::COMMISSION_TYPE_DRB);

                $sponsorDistCommissionledger = new MlmDistCommissionLedger();
                $sponsorDistCommissionledger->setDistId($uplineDist->getDistributorId());
                $sponsorDistCommissionledger->setCommissionType(Globals::COMMISSION_TYPE_DRB);
                $sponsorDistCommissionledger->setTransactionType(Globals::COMMISSION_LEDGER_REGISTER);
                $sponsorDistCommissionledger->setCredit($uplineCommission);
                $sponsorDistCommissionledger->setDebit(0);
                $sponsorDistCommissionledger->setStatusCode(Globals::STATUS_ACTIVE);
                $sponsorDistCommissionledger->setBalance($drbCommissionBalance + $uplineCommission);
                $sponsorDistCommissionledger->setRemark("DRB FOR MEMBER SIGN UP (" . $mlm_distributor->getDistributorCode() . ")");
                $sponsorDistCommissionledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $sponsorDistCommissionledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $sponsorDistCommissionledger->save();

                $mgBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_MG);

                $mlm_account_ledger = new MlmAccountLedger();
                $mlm_account_ledger->setDistId($uplineDist->getDistributorId());
                $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_MG);
                $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_DRB);
                $mlm_account_ledger->setRemark("DRB FOR MEMBER SIGN UP (" . $mlm_distributor->getDistributorCode() . ")");
                $mlm_account_ledger->setCredit($uplineCommission);
                $mlm_account_ledger->setDebit(0);
                $mlm_account_ledger->setBalance($mgBalance + $uplineCommission);
                $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_account_ledger->save();
            }

            $con->commit();
        } catch (PropelException $e) {
            $con->rollback();
            throw $e;
        }

        $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Member Registered Successfully. Your username is " . $fcode));
        return $this->redirect('/member/memberRegistration');
    }

    // **********************************************************************************************
    // *****************************         For broker registeration          **********************
    // **********************************************************************************************
    function generateFcode($countryName)
    {
        $max_digit = 99999999;
        $digit = 8;

        while (true) {
            $fcode = rand(0, $max_digit) . "";
            $fcode = str_pad($fcode, $digit, "0", STR_PAD_LEFT);

            $c = new Criteria();
            $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, "%" . $fcode, Criteria::LIKE);
            $existDist = MlmDistributorPeer::doSelectOne($c);

            if (!$existDist) {
                $c = new Criteria();
                $c->add(MlmRegistrationCountryCodePeer::COUNTRY_NAME, $countryName);
                $mlmRegistrationCountryCode = MlmRegistrationCountryCodePeer::doSelectOne($c);

                if (!$mlmRegistrationCountryCode) {
                    $c = new Criteria();
                    $c->add(MlmRegistrationCountryCodePeer::COUNTRY_NAME, Globals::COUNTRY_OTHER);
                    $mlmRegistrationCountryCode = MlmRegistrationCountryCodePeer::doSelectOne($c);
                }

                $fcode = "MCI" . $fcode . $mlmRegistrationCountryCode->getPrefix();
                break;
            }
        }
        return $fcode;
    }

    // **********************************************************************************************
    // *******************   ~ end      For broker registeration       end ~   **********************
    // **********************************************************************************************

    public function executeVerifySameGroupSponsorId()
    {
        //var_dump($this->getUser()->getAttribute(Globals::SESSION_USERNAME));
        $sponsorId = $this->getRequestParameter('sponsorId');
        //var_dump($sponsorId);

        $c = new Criteria();
        $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $sponsorId);
        $existDist = MlmDistributorPeer::doSelectOne($c);

        $array = explode(',', Globals::STATUS_ACTIVE . "," . Globals::STATUS_PENDING);
        $c = new Criteria();
        $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $sponsorId);
        $c->add(MlmDistributorPeer::PLACEMENT_TREE_STRUCTURE, "%|" . $this->getUser()->getAttribute(Globals::SESSION_DISTID) . "|%", Criteria::LIKE);
        $c->add(MlmDistributorPeer::STATUS_CODE, $array, Criteria::IN);
        $existUser = MlmDistributorPeer::doSelectOne($c);

        $arr = "";
        if (!$existUser && $existDist) {
            $c = new Criteria();
            $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $this->getUser()->getAttribute(Globals::SESSION_USERNAME));
            $c->add(MlmDistributorPeer::PLACEMENT_TREE_STRUCTURE, "%|" . $existDist->getDistributorId() . "|%", Criteria::LIKE);
            $c->add(MlmDistributorPeer::STATUS_CODE, $array, Criteria::IN);
            $existUser = MlmDistributorPeer::doSelectOne($c);

            if ($existUser) {
                $array = explode(',', Globals::STATUS_ACTIVE . "," . Globals::STATUS_PENDING);
                $c = new Criteria();
                $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $sponsorId);
                $c->add(MlmDistributorPeer::STATUS_CODE, $array, Criteria::IN);
                $existUser = MlmDistributorPeer::doSelectOne($c);
            }
        }

        if ($existUser) {
            //if ($existUser->getDistributorId() <> $this->getUser()->getAttribute(Globals::SESSION_DISTID)) {
            $arr = array(
                'userId' => $existUser->getDistributorId(),
                'userName' => $existUser->getDistributorCode(),
                'fullname' => $existUser->getFullName(),
                'nickname' => $existUser->getNickname()
            );
            //}
        }

        echo json_encode($arr);
        return sfView::HEADER_ONLY;
    }

    public function executeVerifySponsorId()
    {
        $sponsorId = $this->getRequestParameter('sponsorId');

        $arr = "";
        if ($sponsorId != "") {
            $array = explode(',', Globals::STATUS_ACTIVE . "," . Globals::STATUS_PENDING);
            $c = new Criteria();
            $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $sponsorId);
            $c->add(MlmDistributorPeer::STATUS_CODE, $array, Criteria::IN);
            $existUser = MlmDistributorPeer::doSelectOne($c);

            if ($existUser) {
                //if ($existUser->getDistributorId() <> $this->getUser()->getAttribute(Globals::SESSION_DISTID)) {
                $arr = array(
                    'userId' => $existUser->getDistributorId(),
                    'userName' => $existUser->getDistributorCode(),
                    'fullname' => $existUser->getFullName(),
                    'nickname' => $existUser->getNickname()
                );
                //}
            }
        }

        echo json_encode($arr);
        return sfView::HEADER_ONLY;
    }

    function generatePaymentReference()
    {
        $max_digit = 9999999999;
        $digit = 12;

        while (true) {
            $fcode = rand(1000000000, $max_digit) . "";
            //$fcode = str_pad($fcode, $digit, "0", STR_PAD_LEFT);
            /*
            for ($x=0; $x < ($digit - strlen($fcode)); $x++) {
                $fcode = "0".$fcode;
            }
			*/
            $c = new Criteria();
            $c->add(MlmDistEpointPurchasePeer::PAYMENT_REFERENCE, $fcode);
            $existCode = MlmDistEpointPurchasePeer::doSelectOne($c);

            if (!$existCode) {
                break;
            }
        }
        return $fcode;
    }

    public function executeVerifyActiveSponsorId()
    {
        $sponsorId = $this->getRequestParameter('sponsorId');

        //$array = explode(',', Globals::STATUS_ACTIVE.",".Globals::STATUS_PENDING);
        $c = new Criteria();
        $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $sponsorId);
        $c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $existUser = MlmDistributorPeer::doSelectOne($c);

        $arr = "";
        if ($existUser) {
            //if ($existUser->getDistributorId() <> $this->getUser()->getAttribute(Globals::SESSION_DISTID)) {
            $arr = array(
                'userId' => $existUser->getDistributorId(),
                'userName' => $existUser->getDistributorCode(),
                'fullname' => $existUser->getFullName(),
                'nickname' => $existUser->getNickname()
            );
            //}
        }

        echo json_encode($arr);
        return sfView::HEADER_ONLY;
    }

    public function executeFetchPackage()
    {
        $account = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_EPOINT);
        $ecashAccount = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);

        $arr = array(
            'packageId' => 0,
            'point' => 0,
            'ecash' => 0,
            'package' => ""
        );
        if (($account) && ($ecashAccount)) {
            $max = $ecashAccount->getBalance();

            if ($account->getBalance() > $ecashAccount->getBalance())
                $max = $account->getBalance();

            $c = new Criteria();
            //$c->add(MlmPackagePeer::PRICE, $max, Criteria::LESS_EQUAL);
            if ($this->getRequestParameter('publicPurchase') == "") {
                $c->add(MlmPackagePeer::PUBLIC_PURCHASE, Globals::YES);
            } else {
                $c->add(MlmPackagePeer::PUBLIC_PURCHASE, $this->getRequestParameter('publicPurchase'));
            }

            $c->addDescendingOrderByColumn(MlmPackagePeer::PRICE);

            $packages = MlmPackagePeer::doSelect($c);

            $packageArray = array();
            $count = 0;
            foreach ($packages as $package) {
                $packageArray[$count]["packageId"] = $package->getPackageId();
                $packageArray[$count]["name"] = $this->getContext()->getI18N()->__($package->getPackageName());
                $packageArray[$count]["price"] = $package->getPrice();
                $count++;
            }

            $arr = array(
                'point' => $account->getBalance(),
                'ecash' => $ecashAccount->getBalance(),
                'package' => $packageArray
            );
        }

        echo json_encode($arr);
        return sfView::HEADER_ONLY;
    }

    public function executeFetchTopupPackage()
    {
        $account = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_EPOINT);
        $ecashAccount = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);

        $distDB = MlmDistributorPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $this->forward404Unless($distDB);

        $distPackage = MlmPackagePeer::retrieveByPK($distDB->getRankId());
        $currentPackageAmount = $distPackage->getPrice();

        if ($currentPackageAmount == null)
            $currentPackageAmount = 99999999;

        $arr = array(
            'packageId' => 0,
            'point' => 0,
            'ecash' => 0,
            'package' => ""
        );
        if (($account) && ($ecashAccount)) {
            $totalEcash = $ecashAccount->getBalance() + $currentPackageAmount;
            $totalEpoint = $account->getBalance() + $currentPackageAmount;

            $max = $totalEcash;

            if ($totalEpoint > $totalEcash)
                $max = $totalEpoint;

            $c = new Criteria();
            $c->add(MlmPackagePeer::PRICE, $max, Criteria::LESS_EQUAL);
            $c->addAnd(MlmPackagePeer::PRICE, $currentPackageAmount, Criteria::GREATER_THAN);
            $c->addAnd(MlmPackagePeer::PRICE, null, Criteria::ISNOTNULL);

            if ($this->getRequestParameter('publicPurchase') == "") {
                $c->add(MlmPackagePeer::PUBLIC_PURCHASE, Globals::YES);
            } else {
                $c->add(MlmPackagePeer::PUBLIC_PURCHASE, $this->getRequestParameter('publicPurchase'));
            }

            $c->addDescendingOrderByColumn(MlmPackagePeer::PRICE);

            $packages = MlmPackagePeer::doSelect($c);

            $packageArray = array();
            $count = 0;
            foreach ($packages as $package) {
                $packageArray[$count]["packageId"] = $package->getPackageId();
                $packageArray[$count]["name"] = $this->getContext()->getI18N()->__($package->getPackageName());
                $packageArray[$count]["price"] = $package->getPrice() - $currentPackageAmount;
                $count++;
            }

            $arr = array(
                'point' => $totalEpoint,
                'ecash' => $totalEcash,
                'package' => $packageArray
            );
        }

        echo json_encode($arr);
        return sfView::HEADER_ONLY;
    }

    public function executeSummary()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "SUMMARY");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "SUMMARY");

        $c = new Criteria();
        $c->add(MlmIbPeer::DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $c->add(MlmIbPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $mlmIb = MlmIbPeer::doSelectOne($c);

        if ($mlmIb) {
            $c = new Criteria();
            //$c->add(MlmDistributorPeer::UPLINE_DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $c->add(MlmDistributorPeer::REGISTERED_IB, $mlmIb->getIbId());
            $c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_PENDING);
            $this->pendingDistributors = MlmDistributorPeer::doSelect($c);
        }

        $c = new Criteria();
        $c->add(MlmAnnouncementPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $c->addDescendingOrderByColumn(MlmAnnouncementPeer::CREATED_ON);
        $c->setLimit(5);
        $this->announcements = MlmAnnouncementPeer::doSelect($c);

        $distributor = MlmDistributorPeer::retrieveByPK($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        if (!$distributor)
            return $this->redirect('/home/logout');

        $ecash = 0;
        $epoint = 0;
        $totalNetworks = 0;
        $ranking = "";
        $currencyCode = "RMB";

        /*$c = new Criteria();
        $c->add(AppSettingPeer::SETTING_PARAMETER, Globals::SETTING_SYSTEM_CURRENCY);
        $settingDB = AppSettingPeer::doSelectOne($c);
        if ($settingDB) {
            $currencyCode = $settingDB->getSettingValue();
        }*/
        if ($distributor) {
            $existUser = AppUserPeer::retrieveByPK($distributor->getUserId());

            if ($existUser) {
                $lastLogin = $existUser->getLastLoginDatetime();
            }

            /*$packageDB = MlmPackagePeer::retrieveByPK($distributor->getRankId());
            if ($packageDB) {
                $ranking = $packageDB->getPackageName();
            }*/

            //$ecash = $this->getAccountBalance($distributor->getDistributorId(), Globals::ACCOUNT_TYPE_ECASH);
            //$epoint = $this->getAccountBalance($distributor->getDistributorId(), Globals::ACCOUNT_TYPE_EPOINT);

            $c = new Criteria();
            $c->add(MlmDistributorPeer::UPLINE_DIST_ID, $distributor->getDistributorId());
            $c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
            $totalNetworks = MlmDistributorPeer::doCount($c);
        }

        $this->ecash = $ecash;
        $this->epoint = $epoint;
        $this->totalNetworks = $totalNetworks;
        $this->ranking = $ranking;
        $this->colorArr = $this->getRankColorArr();
        $this->currencyCode = $currencyCode;
        $this->distributor = $distributor;
        $this->lastLogin = $lastLogin;
        $this->mlmIb = $mlmIb;
    }

    public function executeAnnouncementList()
    {
        $sColumns = $this->getRequestParameter('sColumns');
        $aColumns = explode(",", $sColumns);

        $iColumns = $this->getRequestParameter('iColumns');

        $offset = $this->getRequestParameter('iDisplayStart');
        $sEcho = $this->getRequestParameter('sEcho');
        $limit = $this->getRequestParameter('iDisplayLength');
        $arr = array();

        /******   total records  *******/
        $c = new Criteria();
        $c->add(MlmAnnouncementPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $totalRecords = MlmAnnouncementPeer::doCount($c);

        /******   total filtered records  *******/
        /*if ($this->getRequestParameter('filterAction') != "") {
            $c->addAnd(MlmAnnouncementPeer::TRANSACTION_TYPE, "%" . $this->getRequestParameter('filterAction') . "%", Criteria::LIKE);
        }*/
        $totalFilteredRecords = MlmAnnouncementPeer::doCount($c);

        /******   sorting  *******/
        for ($i = 0; $i < intval($this->getRequestParameter('iSortingCols')); $i++)
        {
            if ($this->getRequestParameter('bSortable_' . intval($this->getRequestParameter('iSortCol_' . $i))) == "true") {
                if ("asc" == $this->getRequestParameter('sSortDir_' . $i)) {
                    $c->addAscendingOrderByColumn($aColumns[intval($this->getRequestParameter('iSortCol_' . $i))]);
                } else {
                    $c->addDescendingOrderByColumn($aColumns[intval($this->getRequestParameter('iSortCol_' . $i))]);
                }
            }
        }

        /******   pagination  *******/
        $pager = new sfPropelPager('MlmAnnouncement', $limit);
        $pager->setCriteria($c);
        $pager->setPage(($offset / $limit) + 1);
        $pager->init();

        foreach ($pager->getResults() as $result) {
            $title = $result->getTitle();
            $createdOn = $result->getCreatedOn();

            if ($this->getUser()->getCulture() == "cn") {
                $title = $result->getTitleCn();
                $createdOn = $result->getCreatedOn();
            }
            $arr[] = array(
                $result->getAnnouncementId(),
                $title,
                $createdOn
            );
        }

        $output = array(
            "sEcho" => intval($sEcho),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalFilteredRecords,
            "aaData" => $arr
        );
        echo json_encode($output);

        return sfView::HEADER_ONLY;
    }

    public function executeBonusDetailList()
    {
        $sColumns = $this->getRequestParameter('sColumns');
        $aColumns = explode(",", $sColumns);

        $iColumns = $this->getRequestParameter('iColumns');

        $offset = $this->getRequestParameter('iDisplayStart');
        $sEcho = $this->getRequestParameter('sEcho');
        $limit = $this->getRequestParameter('iDisplayLength');
        $arr = array();

        /******   total records  *******/
        $c = new Criteria();
        $c->add(MlmDistCommissionLedgerPeer::DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
        //$c->add(MlmDistCommissionLedgerPeer::COMMISSION_TYPE, Globals::ACCOUNT_LEDGER_ACTION_DRB);
        $c->add(MlmDistCommissionLedgerPeer::COMMISSION_TYPE, $this->getRequestParameter('filterBonusType'));
        $totalRecords = MlmDistCommissionLedgerPeer::doCount($c);

        /******   total filtered records  *******/
        if ($this->getRequestParameter('filterMonth') != "" && $this->getRequestParameter('filterYear') != "") {
            $dateUtil = new DateUtil();
            $month = $this->getRequestParameter('filterMonth');
            $year = $this->getRequestParameter('filterYear');
            $d = $dateUtil->getMonth($month, $year);

            $firstOfMonth = date('Y-m-j', $d["first_of_month"]) . " 00:00:00";
            $lastOfMonth = date('Y-m-j', $d["last_of_month"]) . " 23:59:59";

            $c->add(MlmDistCommissionLedgerPeer::CREATED_ON, $firstOfMonth, Criteria::GREATER_EQUAL);
            $c->addAnd(MlmDistCommissionLedgerPeer::CREATED_ON, $lastOfMonth, Criteria::LESS_EQUAL);
        }

        $totalFilteredRecords = MlmDistCommissionLedgerPeer::doCount($c);

        /******   sorting  *******/
        for ($i = 0; $i < intval($this->getRequestParameter('iSortingCols')); $i++)
        {
            if ($this->getRequestParameter('bSortable_' . intval($this->getRequestParameter('iSortCol_' . $i))) == "true") {
                if ("asc" == $this->getRequestParameter('sSortDir_' . $i)) {
                    $c->addAscendingOrderByColumn($aColumns[intval($this->getRequestParameter('iSortCol_' . $i))]);
                } else {
                    $c->addDescendingOrderByColumn($aColumns[intval($this->getRequestParameter('iSortCol_' . $i))]);
                }
            }
        }

        /******   pagination  *******/
        $pager = new sfPropelPager('MlmDistCommissionLedger', $limit);
        $pager->setCriteria($c);
        $pager->setPage(($offset / $limit) + 1);
        $pager->init();

        foreach ($pager->getResults() as $result) {
            $extraRemark = "";
            if (Globals::STATUS_COMPLETE == $result->getStatusCode()) {
                $dateString = $result->getUpdatedOn();
                $dateArr = explode(" ", $dateString);
                $extraRemark = "Successful credited into MT4 Fund (" . $dateArr[0] . ")";
            }
            $arr[] = array(
                $result->getCommissionId() == null ? "0" : $result->getCommissionId(),
                $result->getCreatedOn() == null ? "" : $result->getCreatedOn(),
                $result->getCommissionType() == null ? ""
                        : $this->getContext()->getI18N()->__($result->getCommissionType()),
                $result->getCredit() == null ? "0" : number_format($result->getCredit(), 2),
                $result->getDebit() == null ? "0" : number_format($result->getDebit(), 2),
                $result->getRemark() == null ? "" : $result->getRemark(),
                $extraRemark
            );
        }

        $output = array(
            "sEcho" => intval($sEcho),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalFilteredRecords,
            "aaData" => $arr
        );
        echo json_encode($output);

        return sfView::HEADER_ONLY;
    }

    public function executeDelete()
    {
        $c = new Criteria();
        $c->add(MlmDistributorPeer::UPLINE_DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $c->addAnd(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_PENDING);
        $c->addAnd(MlmDistributorPeer::DISTRIBUTOR_ID, $this->getRequestParameter('distid'));
        $mlmDistributor = MlmDistributorPeer::doSelectOne($c);
        if (!$mlmDistributor)
            return $this->redirect('/home/logout');

        $mlmDistributor->setStatusCode(Globals::STATUS_CANCEL);
        $mlmDistributor->save();

        $appUser = AppUserPeer::retrieveByPk($mlmDistributor->getUserId());
        if (!$appUser)
            return $this->redirect('/home/logout');
        $appUser->setStatusCode(Globals::STATUS_CANCEL);
        $appUser->save();

        return $this->redirect('/member/summary');
    }

    public function executeVerifyTransactionPassword()
    {
        $array = explode(',', Globals::STATUS_ACTIVE . "," . Globals::STATUS_PENDING);
        $c = new Criteria();
        $c->add(AppUserPeer::USER_ID, $this->getUser()->getAttribute(Globals::SESSION_USERID));
        $c->add(AppUserPeer::USERPASSWORD2, $this->getRequestParameter('transactionPassword'));
        $c->add(AppUserPeer::USER_ROLE, Globals::ROLE_DISTRIBUTOR);
        //$c->add(AppUserPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $c->add(AppUserPeer::STATUS_CODE, $array, Criteria::IN);

        $existUser = AppUserPeer::doSelectOne($c);

        if ($existUser) {
            echo 'true';
        } else {
            echo 'false';
        }

        return sfView::HEADER_ONLY;
    }

    public function executeVerifyIB()
    {
        $c = new Criteria();
        $c->add(MlmIbPeer::IB_CODE, $this->getRequestParameter('reg_ib'));
        $c->add(MlmIbPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $exist = MlmIbPeer::doSelectOne($c);

        if ($exist) {
            echo 'true';
        } else {
            echo 'false';
        }

        return sfView::HEADER_ONLY;
    }

    public function executeVerifyClub()
    {
        $c = new Criteria();
        $c->add(MlmIbPeer::CLUB_CODE, $this->getRequestParameter('club_code'));
        $c->add(MlmIbPeer::IS_CLUB, "Y");
        $c->add(MlmIbPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $exist = MlmIbPeer::doSelectOne($c);

        if ($exist) {
            echo 'true';
        } else {
            echo 'false';
        }

        return sfView::HEADER_ONLY;
    }

    public function executeActivateMember()
    {
        $memberId = $this->getRequestParameter('memberId');
        $doAction = $this->getRequestParameter('doAction');

        $error = false;
        $errorMsg = "";

        $c = new Criteria();
        $c->add(MlmIbPeer::DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $c->add(MlmIbPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $mlmIb = MlmIbPeer::doSelectOne($c);

        if (!$mlmIb) {
            $this->setFlash('errorMsg', "You are not valid outlet");
            return $this->redirect('/member/activeMember');
        }

        $mpBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_MP);

        if ($mpBalance < Globals::BASIC_MEMBER_CHARGE) {
            $this->setFlash('errorMsg', "In-sufficient MP");
            return $this->redirect('/member/activeMember');
        }


        $distDB = MlmDistributorPeer::retrieveByPK($memberId);
        if (!$distDB) {
            $this->setFlash('errorMsg', "Invalid member ID");
            return $this->redirect('/member/activeMember');
        }

        $appUserDB = AppUserPeer::retrieveByPK($distDB->getUserId());
        if (!$appUserDB) {
            $this->setFlash('errorMsg', "Invalid User ID");
            return $this->redirect('/member/activeMember');
        }

        $con = Propel::getConnection(MlmDistributorPeer::DATABASE_NAME);
        try {
            $con->begin();

            if ("ACTIVE" == $doAction) {
                $appUserDB->setStatusCode(Globals::STATUS_ACTIVE);
                $distDB->setPackagePurchaseFlag("Y");
                $distDB->setActiveDatetime(date("Y/m/d h:i:s A"));
                $distDB->setStatusCode(Globals::STATUS_ACTIVE);

                $this->setFlash('successMsg', "Member activated Successfully.");
            } else if ("DELETE" == $doAction) {
                $appUserDB->setStatusCode(Globals::STATUS_CANCEL);
                $distDB->setStatusCode(Globals::STATUS_CANCEL);

                $this->setFlash('warningMsg', "Member deleted Successfully.");
            }
            $appUserDB->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $appUserDB->save();

            $distDB->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $distDB->save();

            if ("ACTIVE" == $doAction && $distDB->getUplineDistId() != null && $distDB->getUplineDistId() != "") {
                $uplineDist = MlmDistributorPeer::retrieveByPK($distDB->getUplineDistId());

                if ($uplineDist) {
                    $mlm_account_ledger = new MlmAccountLedger();
                    $mlm_account_ledger->setDistId($uplineDist->getDistributorId());
                    $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_MP);
                    $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_REGISTER);
                    $mlm_account_ledger->setRemark("DRB FOR MEMBER SIGN UP (" . $distDB->getDistributorCode() . ")");
                    $mlm_account_ledger->setCredit(0);
                    $mlm_account_ledger->setDebit(Globals::BASIC_MEMBER_CHARGE);
                    $mlm_account_ledger->setBalance($mpBalance - Globals::BASIC_MEMBER_CHARGE);
                    $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $mlm_account_ledger->save();

                    $uplineCommission = Globals::BASIC_MEMBER_CHARGE * 0.3;

                    $drbCommissionBalance = $this->getCommissionBalance($uplineDist->getDistributorId(), Globals::COMMISSION_TYPE_DRB);

                    $sponsorDistCommissionledger = new MlmDistCommissionLedger();
                    $sponsorDistCommissionledger->setDistId($uplineDist->getDistributorId());
                    $sponsorDistCommissionledger->setCommissionType(Globals::COMMISSION_TYPE_DRB);
                    $sponsorDistCommissionledger->setTransactionType(Globals::COMMISSION_LEDGER_REGISTER);
                    $sponsorDistCommissionledger->setCredit($uplineCommission);
                    $sponsorDistCommissionledger->setDebit(0);
                    $sponsorDistCommissionledger->setStatusCode(Globals::STATUS_ACTIVE);
                    $sponsorDistCommissionledger->setBalance($drbCommissionBalance + $uplineCommission);
                    $sponsorDistCommissionledger->setRemark("DRB FOR MEMBER SIGN UP (" . $distDB->getDistributorCode() . ")");
                    $sponsorDistCommissionledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $sponsorDistCommissionledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $sponsorDistCommissionledger->save();

                    $mgBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_MG);

                    $mlm_account_ledger = new MlmAccountLedger();
                    $mlm_account_ledger->setDistId($uplineDist->getDistributorId());
                    $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_MG);
                    $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_DRB);
                    $mlm_account_ledger->setRemark("DRB FOR MEMBER SIGN UP (" . $distDB->getDistributorCode() . ")");
                    $mlm_account_ledger->setCredit($uplineCommission);
                    $mlm_account_ledger->setDebit(0);
                    $mlm_account_ledger->setBalance($mgBalance + $uplineCommission);
                    $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $mlm_account_ledger->save();

                    /* ****************************************************
                    * card_mp_return
                    * ***************************************************/
                    $dateUtil = new DateUtil();
                    $currentDate = $dateUtil->formatDate("Y-m-d", $distDB->getActiveDatetime()) . " 00:00:00";
                    $currentDate_timestamp = strtotime($currentDate);
                    $dividendDate = strtotime("+1 months", $currentDate_timestamp);

                    $cardMpReturn = new CardMpReturn();
                    $cardMpReturn->setDistId($distDB->getDistributorId());
                    $cardMpReturn->setIdx(1);
                    $cardMpReturn->setReturnDate(date("Y-m-d h:i:s", $dividendDate));
                    $cardMpReturn->setFirstReturnDate(date("Y-m-d h:i:s", $dividendDate));
                    $cardMpReturn->setStatusCode(Globals::DIVIDEND_STATUS_PENDING);
                    $cardMpReturn->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $cardMpReturn->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $cardMpReturn->save();
                }
            }

            $con->commit();
        } catch (PropelException $e) {
            $con->rollback();
            throw $e;
        }

        return $this->redirect('/member/activeMember');
    }

    public function executePlacementTree()
    {
        $c = new Criteria();
        //$c->add(MlmPackagePeer::PUBLIC_PURCHASE, 1);
        $this->packageDBs = MlmPackagePeer::doSelect($c);

        if ($this->getRequestParameter('doAction') == "save") {
            $uplineDistCode = $this->getRequestParameter('uplineDistCode');
            $uplinePosition = $this->getRequestParameter('uplinePosition');
            $sponsorDistId = $this->getRequestParameter('sponsorDistId');

            $uplineDistDB = $this->getDistributorInformation($uplineDistCode);
            $distDB = MlmDistributorPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $sponsorDB = MlmDistributorPeer::retrieveByPk($sponsorDistId);
            $this->forward404Unless($distDB);
            $this->forward404Unless($sponsorDB);

            $treeStructure = $uplineDistDB->getPlacementTreeStructure() . "|" . $sponsorDB->getDistributorId() . "|";
            $treeLevel = $uplineDistDB->getPlacementTreeLevel() + 1;

            $sponsorDB->setPlacementDatetime(date("Y/m/d h:i:s A"));
            $sponsorDB->setPlacementPosition($uplinePosition);
            //$sponsorDB->setUplineDistId($uplineDistDB->getDistributorId());
            //$sponsorDB->setUplineDistCode($uplineDistDB->getDistributorCode());
            $sponsorDB->setPlacementTreeStructure($treeStructure);
            $sponsorDB->setPlacementTreeLevel($treeLevel);
            $sponsorDB->setTreeUplineDistId($uplineDistDB->getDistributorId());
            $sponsorDB->setTreeUplineDistCode($uplineDistDB->getDistributorCode());
            $sponsorDB->save();

            $sponsoredPackageDB = MlmPackagePeer::retrieveByPK($sponsorDB->getRankId());
            $this->forward404Unless($sponsoredPackageDB);
            $pairingPoint = $sponsoredPackageDB->getPrice();
            /*if ($sponsoredPackageDB->getPackageId() == Globals::MAX_PACKAGE_ID) {
                $pairingPoint = $amountNeeded;
            }*/
            // recalculate Total left and total right for $uplineDistDB
            $arrs = explode("|", $uplineDistDB->getPlacementTreeStructure());
            for ($x = count($arrs); $x > 0; $x--) {
                if ($arrs[$x] == "") {
                    continue;
                }
                $uplineDistDB = $this->getDistributorInformation($arrs[$x]);
                $this->forward404Unless($uplineDistDB);
                $totalLeft = $this->getTotalPosition($arrs[$x], Globals::PLACEMENT_LEFT);
                $totalRight = $this->getTotalPosition($arrs[$x], Globals::PLACEMENT_RIGHT);
                $uplineDistDB->setTotalLeft($totalLeft);
                $uplineDistDB->setTotalRight($totalRight);
                $uplineDistDB->save();
            }

            /******************************/
            /*  store Pairing points
            /******************************/
            if ($sponsorDB->getTreeUplineDistId() != 0 && $sponsorDB->getTreeUplineDistCode() != null) {
                $level = 0;
                $uplineDistDB = MlmDistributorPeer::retrieveByPk($sponsorDB->getTreeUplineDistId());
                $sponsoredDistributorCode = $sponsorDB->getDistributorCode();
                while ($level < 100) {
                    //var_dump($uplineDistDB->getUplineDistId());
                    //var_dump($uplineDistDB->getUplineDistCode());
                    //print_r("<br>");
                    $c = new Criteria();
                    $c->add(MlmDistPairingPeer::DIST_ID, $uplineDistDB->getDistributorId());
                    $sponsorDistPairingDB = MlmDistPairingPeer::doSelectOne($c);

                    $addToLeft = 0;
                    $addToRight = 0;
                    $leftBalance = 0;
                    $rightBalance = 0;
                    if (!$sponsorDistPairingDB) {
                        $sponsorDistPairingDB = new MlmDistPairing();
                        $sponsorDistPairingDB->setDistId($uplineDistDB->getDistributorId());

                        $packageDB = MlmPackagePeer::retrieveByPK($uplineDistDB->getRankId());
                        $this->forward404Unless($packageDB);

                        $sponsorDistPairingDB->setLeftBalance($leftBalance);
                        $sponsorDistPairingDB->setRightBalance($rightBalance);
                        $sponsorDistPairingDB->setFlushLimit($packageDB->getDailyMaxPairing());
                        $sponsorDistPairingDB->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    } else {
                        $leftBalance = $sponsorDistPairingDB->getLeftBalance();
                        $rightBalance = $sponsorDistPairingDB->getRightBalance();
                    }
                    $sponsorDistPairingDB->setLeftBalance($leftBalance + $addToLeft);
                    $sponsorDistPairingDB->setRightBalance($rightBalance + $addToRight);
                    $sponsorDistPairingDB->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $sponsorDistPairingDB->save();

                    $c = new Criteria();
                    $c->add(MlmDistPairingLedgerPeer::DIST_ID, $uplineDistDB->getDistributorId());
                    $c->add(MlmDistPairingLedgerPeer::LEFT_RIGHT, $uplinePosition);
                    $c->addDescendingOrderByColumn(MlmDistPairingLedgerPeer::CREATED_ON);
                    $sponsorDistPairingLedgerDB = MlmDistPairingLedgerPeer::doSelectOne($c);

                    $legBalance = 0;
                    if ($sponsorDistPairingLedgerDB) {
                        $legBalance = $sponsorDistPairingLedgerDB->getBalance();
                    }

                    $sponsorDistPairingledger = new MlmDistPairingLedger();
                    $sponsorDistPairingledger->setDistId($uplineDistDB->getDistributorId());
                    $sponsorDistPairingledger->setLeftRight($uplinePosition);
                    $sponsorDistPairingledger->setTransactionType(Globals::PAIRING_LEDGER_REGISTER);
                    $sponsorDistPairingledger->setCredit($pairingPoint);
                    $sponsorDistPairingledger->setDebit(0);
                    $sponsorDistPairingledger->setBalance($legBalance + $pairingPoint);
                    $sponsorDistPairingledger->setRemark("PAIRING POINT AMOUNT (" . $sponsoredDistributorCode . ")");
                    $sponsorDistPairingledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $sponsorDistPairingledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $sponsorDistPairingledger->save();

                    $this->revalidatePairing($uplineDistDB->getDistributorId(), $uplinePosition);

                    if ($uplineDistDB->getTreeUplineDistId() == 0 || $uplineDistDB->getTreeUplineDistCode() == null) {
                        break;
                    }

                    $uplinePosition = $uplineDistDB->getPlacementPosition();
                    $uplineDistDB = MlmDistributorPeer::retrieveByPk($uplineDistDB->getTreeUplineDistId());
                    $level++;
                }
            }
            /******************************/
            /*  Pairing             ~ END ~
            /******************************/
            return $this->redirect('/member/placementTree?distcode=' . $this->getRequestParameter('distcode', $this->getUser()->getAttribute(Globals::SESSION_USERNAME)));
        }
        $distcode = $this->getRequestParameter('distcode', $this->getUser()->getAttribute(Globals::SESSION_USERNAME));
        $pageDirection = $this->getRequestParameter('p', "");

        $this->pageDirection = $pageDirection;
        $anode = array();
        //      0
        //  1       2
        //3   4   5   6

        // TO_HIDE_DIST_GROUP
        $hideDistGroup = false;
        $pos = strrpos(Globals::TO_HIDE_DIST_GROUP, $this->getUser()->getAttribute(Globals::SESSION_USERNAME));
        if ($pos === false) { // note: three equal signs

        } else {
            $hideDistGroup = true;
        }
        $this->hideDistGroup = $hideDistGroup;
        // TO_HIDE_DIST_GROUP end ~

        $c = new Criteria();
        $c1 = $c->getNewCriterion(MlmDistributorPeer::DISTRIBUTOR_CODE, $distcode);
        $c2 = $c->getNewCriterion(MlmDistributorPeer::NICKNAME, $distcode);
        $c1->addOr($c2);
        $c->add($c1);
        //$c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $distcode);
        $c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $c->add(MlmDistributorPeer::PLACEMENT_TREE_STRUCTURE, "%|" . $this->getUser()->getAttribute(Globals::SESSION_DISTID) . "|%", Criteria::LIKE);
        $distDB = MlmDistributorPeer::doSelectOne($c);

        if (!$distDB) {
            $this->errorSearch = true;
            $c = new Criteria();
            $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $this->getUser()->getAttribute(Globals::SESSION_USERNAME));
            $distDB = MlmDistributorPeer::doSelectOne($c);
        }

        // TO_HIDE_DIST_GROUP
        if ($hideDistGroup) {
            $pos = strrpos($distDB->getPlacementTreeStructure(), Globals::HIDE_DIST_GROUP);
            if ($pos === false) { // note: three equal signs

            } else {
                $this->errorSearch = true;
                $c = new Criteria();
                $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $this->getUser()->getAttribute(Globals::SESSION_USERNAME));
                $distDB = MlmDistributorPeer::doSelectOne($c);
            }
        }
        // TO_HIDE_DIST_GROUP end ~

        $leftOnePlacement = $this->getPlacementDistributorInformation($distDB->getDistributorId(), Globals::PLACEMENT_LEFT);
        $rightTwoPlacement = $this->getPlacementDistributorInformation($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT);
        $anode[0]["distCode"] = $distDB->getDistributorCode();
        $anode[0]["_self"] = $distDB;
        $anode[0]["_left"] = $leftOnePlacement;
        $anode[0]["_right"] = $rightTwoPlacement;
        $anode[0]["_available"] = false;
        $anode[0]["_left_this_month_sales"] = $this->getThisMonthSales($distDB->getDistributorId(), Globals::PLACEMENT_LEFT);
        $anode[0]["_right_this_month_sales"] = $this->getThisMonthSales($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT);
        $anode[0]["_dist_pairing_ledger"] = $this->queryDistPairing($distDB->getDistributorId());
        $anode[0]["_accumulate_left"] = $this->getLegTotalMember($leftOnePlacement);
        $anode[0]["_accumulate_right"] = $this->getLegTotalMember($rightTwoPlacement);
        //        $anode[0]["_accumulate_left"] = $this->getAccumulateGroupBvs($distDB->getDistributorId(), Globals::PLACEMENT_LEFT);
        //        $anode[0]["_accumulate_right"] = $this->getAccumulateGroupBvs($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT);
        $anode[0]["_today_left"] = $this->getTodaySales($distDB->getDistributorId(), Globals::PLACEMENT_LEFT);
        $anode[0]["_today_right"] = $this->getTodaySales($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT);
        $anode[0]["_carry_left"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_LEFT, null) - $anode[0]["_today_left"];
        $anode[0]["_carry_right"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT, null) - $anode[0]["_today_right"];
        $anode[0]["_sales_left"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_LEFT, null);
        $anode[0]["_sales_right"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT, null);

        if ($leftOnePlacement == null) {
            $anode[1]["distCode"] = "";
            $anode[1]["_self"] = new MlmDistributor();
            $anode[1]["_left"] = null;
            $anode[1]["_right"] = null;
            $anode[1]["_available"] = true;
            $anode[1]["_left_this_month_sales"] = null;
            $anode[1]["_right_this_month_sales"] = null;
            $anode[1]["_dist_pairing_ledger"] = null;
            $anode[1]["_accumulate_left"] = null;
            $anode[1]["_accumulate_right"] = null;
            $anode[1]["_today_left"] = null;
            $anode[1]["_today_right"] = null;
            $anode[1]["_carry_left"] = null;
            $anode[1]["_carry_right"] = null;
            $anode[1]["_sales_left"] = null;
            $anode[1]["_sales_right"] = null;

            $anode[3]["distCode"] = "";
            $anode[3]["_self"] = new MlmDistributor();
            $anode[3]["_left"] = null;
            $anode[3]["_right"] = null;
            $anode[3]["_available"] = false;
            $anode[3]["_left_this_month_sales"] = null;
            $anode[3]["_right_this_month_sales"] = null;
            $anode[3]["_dist_pairing_ledger"] = null;
            $anode[3]["_accumulate_left"] = null;
            $anode[3]["_accumulate_right"] = null;
            $anode[3]["_today_left"] = null;
            $anode[3]["_today_right"] = null;
            $anode[3]["_carry_left"] = null;
            $anode[3]["_carry_right"] = null;
            $anode[3]["_sales_left"] = null;
            $anode[3]["_sales_right"] = null;

            $anode[4]["distCode"] = "";
            $anode[4]["_self"] = new MlmDistributor();
            $anode[4]["_left"] = null;
            $anode[4]["_right"] = null;
            $anode[4]["_available"] = false;
            $anode[4]["_left_this_month_sales"] = null;
            $anode[4]["_right_this_month_sales"] = null;
            $anode[4]["_dist_pairing_ledger"] = null;
            $anode[4]["_accumulate_left"] = null;
            $anode[4]["_accumulate_right"] = null;
            $anode[4]["_today_left"] = null;
            $anode[4]["_today_right"] = null;
            $anode[4]["_carry_left"] = null;
            $anode[4]["_carry_right"] = null;
            $anode[4]["_sales_left"] = null;
            $anode[4]["_sales_right"] = null;

        } else {
            $distDB = $this->getDistributorInformation($leftOnePlacement->getDistributorCode());
            $leftThreePlacement = $this->getPlacementDistributorInformation($distDB->getDistributorId(), Globals::PLACEMENT_LEFT);
            $rightFourPlacement = $this->getPlacementDistributorInformation($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT);

            $anode[1]["distCode"] = $leftOnePlacement->getDistributorCode();
            $anode[1]["_self"] = $distDB;
            $anode[1]["_left"] = $leftThreePlacement;
            $anode[1]["_right"] = $rightFourPlacement;
            $anode[1]["_available"] = false;
            $anode[1]["_left_this_month_sales"] = $this->getThisMonthSales($leftOnePlacement->getDistributorId(), Globals::PLACEMENT_LEFT);
            $anode[1]["_right_this_month_sales"] = $this->getThisMonthSales($leftOnePlacement->getDistributorId(), Globals::PLACEMENT_RIGHT);
            $anode[1]["_dist_pairing_ledger"] = $this->queryDistPairing($leftOnePlacement->getDistributorId());
            $anode[1]["_accumulate_left"] = $this->getLegTotalMember($leftThreePlacement);
            $anode[1]["_accumulate_right"] = $this->getLegTotalMember($rightFourPlacement);
            //            $anode[1]["_accumulate_left"] = $this->getAccumulateGroupBvs($leftOnePlacement->getDistributorId(), Globals::PLACEMENT_LEFT);
            //            $anode[1]["_accumulate_right"] = $this->getAccumulateGroupBvs($leftOnePlacement->getDistributorId(), Globals::PLACEMENT_RIGHT);
            $anode[1]["_today_left"] = $this->getTodaySales($distDB->getDistributorId(), Globals::PLACEMENT_LEFT);
            $anode[1]["_today_right"] = $this->getTodaySales($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT);
            $anode[1]["_carry_left"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_LEFT, null) - $anode[1]["_today_left"];
            $anode[1]["_carry_right"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT, null) - $anode[1]["_today_right"];
            $anode[1]["_sales_left"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_LEFT, null);
            $anode[1]["_sales_right"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT, null);

            if ($leftThreePlacement == null) {
                $anode[3]["distCode"] = "";
                $anode[3]["_self"] = new MlmDistributor();
                $anode[3]["_left"] = null;
                $anode[3]["_right"] = null;
                $anode[3]["_available"] = true;
                $anode[3]["_left_this_month_sales"] = null;
                $anode[3]["_right_this_month_sales"] = null;
                $anode[3]["_dist_pairing_ledger"] = null;
                $anode[3]["_accumulate_left"] = null;
                $anode[3]["_accumulate_right"] = null;
                $anode[3]["_today_left"] = null;
                $anode[3]["_today_right"] = null;
                $anode[3]["_carry_left"] = null;
                $anode[3]["_carry_right"] = null;
                $anode[3]["_sales_left"] = null;
                $anode[3]["_sales_right"] = null;
            } else {
                $distOne = $this->getPlacementDistributorInformation($leftThreePlacement->getDistributorId(), Globals::PLACEMENT_LEFT);
                $distTwo = $this->getPlacementDistributorInformation($leftThreePlacement->getDistributorId(), Globals::PLACEMENT_RIGHT);

                $distDB = $this->getDistributorInformation($leftThreePlacement->getDistributorCode());
                $anode[3]["distCode"] = $leftThreePlacement->getDistributorCode();
                $anode[3]["_self"] = $distDB;
                $anode[3]["_left"] = null;
                $anode[3]["_right"] = null;
                $anode[3]["_available"] = false;
                $anode[3]["_left_this_month_sales"] = $this->getThisMonthSales($leftThreePlacement->getDistributorId(), Globals::PLACEMENT_LEFT);
                $anode[3]["_right_this_month_sales"] = $this->getThisMonthSales($leftThreePlacement->getDistributorId(), Globals::PLACEMENT_RIGHT);
                $anode[3]["_dist_pairing_ledger"] = $this->queryDistPairing($leftThreePlacement->getDistributorId());
                $anode[3]["_accumulate_left"] = $this->getLegTotalMember($distOne);
                $anode[3]["_accumulate_right"] = $this->getLegTotalMember($distTwo);
                //                $anode[3]["_accumulate_left"] = $this->getAccumulateGroupBvs($leftThreePlacement->getDistributorId(), Globals::PLACEMENT_LEFT);
                //                $anode[3]["_accumulate_right"] = $this->getAccumulateGroupBvs($leftThreePlacement->getDistributorId(), Globals::PLACEMENT_RIGHT);
                $anode[3]["_today_left"] = $this->getTodaySales($distDB->getDistributorId(), Globals::PLACEMENT_LEFT);
                $anode[3]["_today_right"] = $this->getTodaySales($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT);
                $anode[3]["_carry_left"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_LEFT, null) - $anode[3]["_today_left"];
                $anode[3]["_carry_right"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT, null) - $anode[3]["_today_right"];
                $anode[3]["_sales_left"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_LEFT, null);
                $anode[3]["_sales_right"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT, null);
            }
            if ($rightFourPlacement == null) {
                $anode[4]["distCode"] = "";
                $anode[4]["_self"] = new MlmDistributor();
                $anode[4]["_left"] = null;
                $anode[4]["_right"] = null;
                $anode[4]["_available"] = true;
                $anode[4]["_left_this_month_sales"] = null;
                $anode[4]["_right_this_month_sales"] = null;
                $anode[4]["_dist_pairing_ledger"] = null;
                $anode[4]["_accumulate_left"] = null;
                $anode[4]["_accumulate_right"] = null;
                $anode[4]["_today_left"] = null;
                $anode[4]["_today_right"] = null;
                $anode[4]["_carry_left"] = null;
                $anode[4]["_carry_right"] = null;
                $anode[4]["_sales_left"] = null;
                $anode[4]["_sales_right"] = null;
            } else {
                $distDB = $this->getDistributorInformation($rightFourPlacement->getDistributorCode());

                $distOne = $this->getPlacementDistributorInformation($rightFourPlacement->getDistributorId(), Globals::PLACEMENT_LEFT);
                $distTwo = $this->getPlacementDistributorInformation($rightFourPlacement->getDistributorId(), Globals::PLACEMENT_RIGHT);

                $anode[4]["distCode"] = $rightFourPlacement->getDistributorCode();
                $anode[4]["_self"] = $distDB;
                $anode[4]["_left"] = null;
                $anode[4]["_right"] = null;
                $anode[4]["_available"] = false;
                $anode[4]["_left_this_month_sales"] = $this->getThisMonthSales($rightFourPlacement->getDistributorId(), Globals::PLACEMENT_LEFT);
                $anode[4]["_right_this_month_sales"] = $this->getThisMonthSales($rightFourPlacement->getDistributorId(), Globals::PLACEMENT_RIGHT);
                $anode[4]["_dist_pairing_ledger"] = $this->queryDistPairing($rightFourPlacement->getDistributorId());

                $anode[4]["_accumulate_left"] = $this->getLegTotalMember($distOne);
                $anode[4]["_accumulate_right"] = $this->getLegTotalMember($distTwo);
                //                $anode[4]["_accumulate_left"] = $this->getAccumulateGroupBvs($rightFourPlacement->getDistributorId(), Globals::PLACEMENT_LEFT);
                //                $anode[4]["_accumulate_right"] = $this->getAccumulateGroupBvs($rightFourPlacement->getDistributorId(), Globals::PLACEMENT_RIGHT);
                $anode[4]["_today_left"] = $this->getTodaySales($distDB->getDistributorId(), Globals::PLACEMENT_LEFT);
                $anode[4]["_today_right"] = $this->getTodaySales($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT);
                $anode[4]["_carry_left"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_LEFT, null) - $anode[4]["_today_left"];
                $anode[4]["_carry_right"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT, null) - $anode[4]["_today_right"];
                $anode[4]["_sales_left"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_LEFT, null);
                $anode[4]["_sales_right"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT, null);
            }
        }

        if ($rightTwoPlacement == null) {
            $anode[2]["distCode"] = "";
            $anode[2]["_self"] = new MlmDistributor();
            $anode[2]["_left"] = null;
            $anode[2]["_right"] = null;
            $anode[2]["_available"] = true;
            $anode[2]["_left_this_month_sales"] = null;
            $anode[2]["_right_this_month_sales"] = null;
            $anode[2]["_dist_pairing_ledger"] = null;
            $anode[2]["_accumulate_left"] = null;
            $anode[2]["_accumulate_right"] = null;
            $anode[2]["_today_left"] = null;
            $anode[2]["_today_right"] = null;
            $anode[2]["_carry_left"] = null;
            $anode[2]["_carry_right"] = null;
            $anode[2]["_sales_left"] = null;
            $anode[2]["_sales_right"] = null;

            $anode[5]["distCode"] = "";
            $anode[5]["_self"] = new MlmDistributor();
            $anode[5]["_left"] = null;
            $anode[5]["_right"] = null;
            $anode[5]["_available"] = false;
            $anode[5]["_left_this_month_sales"] = null;
            $anode[5]["_right_this_month_sales"] = null;
            $anode[5]["_dist_pairing_ledger"] = null;
            $anode[5]["_accumulate_left"] = null;
            $anode[5]["_accumulate_right"] = null;
            $anode[5]["_today_left"] = null;
            $anode[5]["_today_right"] = null;
            $anode[5]["_carry_left"] = null;
            $anode[5]["_carry_right"] = null;
            $anode[5]["_sales_left"] = null;
            $anode[5]["_sales_right"] = null;

            $anode[6]["distCode"] = "";
            $anode[6]["_self"] = new MlmDistributor();
            $anode[6]["_left"] = null;
            $anode[6]["_right"] = null;
            $anode[6]["_available"] = false;
            $anode[6]["_left_this_month_sales"] = null;
            $anode[6]["_right_this_month_sales"] = null;
            $anode[6]["_dist_pairing_ledger"] = null;
            $anode[6]["_accumulate_left"] = null;
            $anode[6]["_accumulate_right"] = null;
            $anode[6]["_today_left"] = null;
            $anode[6]["_today_right"] = null;
            $anode[6]["_carry_left"] = null;
            $anode[6]["_carry_right"] = null;
            $anode[6]["_sales_left"] = null;
            $anode[6]["_sales_right"] = null;
        } else {
            $distDB = $this->getDistributorInformation($rightTwoPlacement->getDistributorCode());
            $leftFivePlacement = $this->getPlacementDistributorInformation($distDB->getDistributorId(), Globals::PLACEMENT_LEFT);
            $rightSixPlacement = $this->getPlacementDistributorInformation($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT);

            $anode[2]["distCode"] = $rightTwoPlacement->getDistributorCode();
            $anode[2]["_self"] = $distDB;
            $anode[2]["_left"] = $leftFivePlacement;
            $anode[2]["_right"] = $rightSixPlacement;
            $anode[2]["_available"] = false;
            $anode[2]["_left_this_month_sales"] = $this->getThisMonthSales($rightTwoPlacement->getDistributorId(), Globals::PLACEMENT_LEFT);
            $anode[2]["_right_this_month_sales"] = $this->getThisMonthSales($rightTwoPlacement->getDistributorId(), Globals::PLACEMENT_RIGHT);
            $anode[2]["_dist_pairing_ledger"] = $this->queryDistPairing($rightTwoPlacement->getDistributorId());

            $anode[2]["_accumulate_left"] = $this->getLegTotalMember($leftFivePlacement);
            $anode[2]["_accumulate_right"] = $this->getLegTotalMember($rightSixPlacement);
            //            $anode[2]["_accumulate_left"] = $this->getAccumulateGroupBvs($rightTwoPlacement->getDistributorId(), Globals::PLACEMENT_LEFT);
            //            $anode[2]["_accumulate_right"] = $this->getAccumulateGroupBvs($rightTwoPlacement->getDistributorId(), Globals::PLACEMENT_RIGHT);
            $anode[2]["_today_left"] = $this->getTodaySales($distDB->getDistributorId(), Globals::PLACEMENT_LEFT);
            $anode[2]["_today_right"] = $this->getTodaySales($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT);
            $anode[2]["_carry_left"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_LEFT, null) - $anode[2]["_today_left"];
            $anode[2]["_carry_right"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT, null) - $anode[2]["_today_right"];
            $anode[2]["_sales_left"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_LEFT, null);
            $anode[2]["_sales_right"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT, null);


            if ($leftFivePlacement == null) {
                $anode[5]["distCode"] = "";
                $anode[5]["_self"] = new MlmDistributor();
                $anode[5]["_left"] = null;
                $anode[5]["_right"] = null;
                $anode[5]["_available"] = true;
                $anode[5]["_left_this_month_sales"] = null;
                $anode[5]["_right_this_month_sales"] = null;
                $anode[5]["_dist_pairing_ledger"] = null;
                $anode[5]["_accumulate_left"] = null;
                $anode[5]["_accumulate_right"] = null;
                $anode[5]["_today_left"] = null;
                $anode[5]["_today_right"] = null;
                $anode[5]["_carry_left"] = null;
                $anode[5]["_carry_right"] = null;
                $anode[5]["_sales_left"] = null;
                $anode[5]["_sales_right"] = null;
            } else {
                $distDB = $this->getDistributorInformation($leftFivePlacement->getDistributorCode());
                $distOne = $this->getPlacementDistributorInformation($leftFivePlacement->getDistributorId(), Globals::PLACEMENT_LEFT);
                $distTwo = $this->getPlacementDistributorInformation($leftFivePlacement->getDistributorId(), Globals::PLACEMENT_RIGHT);
                $anode[5]["distCode"] = $leftFivePlacement->getDistributorCode();
                $anode[5]["_self"] = $distDB;
                $anode[5]["_left"] = null;
                $anode[5]["_right"] = null;
                $anode[5]["_available"] = false;
                $anode[5]["_left_this_month_sales"] = $this->getThisMonthSales($leftFivePlacement->getDistributorId(), Globals::PLACEMENT_LEFT);
                $anode[5]["_right_this_month_sales"] = $this->getThisMonthSales($leftFivePlacement->getDistributorId(), Globals::PLACEMENT_RIGHT);
                $anode[5]["_dist_pairing_ledger"] = $this->queryDistPairing($leftFivePlacement->getDistributorId());

                $anode[5]["_accumulate_left"] = $this->getLegTotalMember($distOne);
                $anode[5]["_accumulate_right"] = $this->getLegTotalMember($distTwo);
                //                $anode[5]["_accumulate_left"] = $this->getAccumulateGroupBvs($leftFivePlacement->getDistributorId(), Globals::PLACEMENT_LEFT);
                //                $anode[5]["_accumulate_right"] = $this->getAccumulateGroupBvs($leftFivePlacement->getDistributorId(), Globals::PLACEMENT_RIGHT);
                $anode[5]["_today_left"] = $this->getTodaySales($distDB->getDistributorId(), Globals::PLACEMENT_LEFT);
                $anode[5]["_today_right"] = $this->getTodaySales($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT);
                $anode[5]["_carry_left"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_LEFT, null) - $anode[5]["_today_left"];
                $anode[5]["_carry_right"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT, null) - $anode[5]["_today_right"];
                $anode[5]["_sales_left"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_LEFT, null);
                $anode[5]["_sales_right"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT, null);
            }
            if ($rightSixPlacement == null) {
                $anode[6]["distCode"] = "";
                $anode[6]["_self"] = new MlmDistributor();
                $anode[6]["_left"] = null;
                $anode[6]["_right"] = null;
                $anode[6]["_available"] = true;
                $anode[6]["_left_this_month_sales"] = null;
                $anode[6]["_right_this_month_sales"] = null;
                $anode[6]["_dist_pairing_ledger"] = null;
                $anode[6]["_accumulate_left"] = null;
                $anode[6]["_accumulate_right"] = null;
                $anode[6]["_today_left"] = null;
                $anode[6]["_today_right"] = null;
                $anode[6]["_carry_left"] = null;
                $anode[6]["_carry_right"] = null;
                $anode[6]["_sales_left"] = null;
                $anode[6]["_sales_right"] = null;
            } else {
                $distDB = $this->getDistributorInformation($rightSixPlacement->getDistributorCode());
                $distOne = $this->getPlacementDistributorInformation($rightSixPlacement->getDistributorId(), Globals::PLACEMENT_LEFT);
                $distTwo = $this->getPlacementDistributorInformation($rightSixPlacement->getDistributorId(), Globals::PLACEMENT_RIGHT);
                $anode[6]["distCode"] = $rightSixPlacement->getDistributorCode();
                $anode[6]["_self"] = $distDB;
                $anode[6]["_left"] = null;
                $anode[6]["_right"] = null;
                $anode[6]["_available"] = false;
                $anode[6]["_left_this_month_sales"] = $this->getThisMonthSales($rightSixPlacement->getDistributorId(), Globals::PLACEMENT_LEFT);
                $anode[6]["_right_this_month_sales"] = $this->getThisMonthSales($rightSixPlacement->getDistributorId(), Globals::PLACEMENT_RIGHT);
                $anode[6]["_dist_pairing_ledger"] = $this->queryDistPairing($rightSixPlacement->getDistributorId());
                $anode[6]["_accumulate_left"] = $this->getLegTotalMember($distOne);
                $anode[6]["_accumulate_right"] = $this->getLegTotalMember($distTwo);
                //                $anode[6]["_accumulate_left"] = $this->getAccumulateGroupBvs($rightSixPlacement->getDistributorId(), Globals::PLACEMENT_LEFT);
                //                $anode[6]["_accumulate_right"] = $this->getAccumulateGroupBvs($rightSixPlacement->getDistributorId(), Globals::PLACEMENT_RIGHT);
                $anode[6]["_today_left"] = $this->getTodaySales($distDB->getDistributorId(), Globals::PLACEMENT_LEFT);
                $anode[6]["_today_right"] = $this->getTodaySales($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT);
                $anode[6]["_carry_left"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_LEFT, null) - $anode[6]["_today_left"];
                $anode[6]["_carry_right"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT, null) - $anode[6]["_today_right"];
                $anode[6]["_sales_left"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_LEFT, null);
                $anode[6]["_sales_right"] = $this->findPairingLedgers($distDB->getDistributorId(), Globals::PLACEMENT_RIGHT, null);
            }
        }

        $this->distcode = $distcode;
        $this->anode = $anode;
        $this->colorArr = $this->getRankColorArr();

        $isTop = false;
        if (strtoupper($distcode) == strtoupper($this->getUser()->getAttribute(Globals::SESSION_USERNAME))) {
            $isTop = true;
        }
        $this->isTop = $isTop;

        //if ($this->getUser()->getAttribute(Globals::SESSION_PLACEMENT_TREE_PASSWORD_REQUIRED, false) == false) {
        //    $this->setTemplate('placementTreeInList');
        //} else
        if ($pageDirection == "stat") {
            $this->setTemplate('placementTreeStat');
        }
    }

    public function executePendingMemberList()
    {
        $sColumns = $this->getRequestParameter('sColumns');
        $aColumns = explode(",", $sColumns);

        $iColumns = $this->getRequestParameter('iColumns');

        $offset = $this->getRequestParameter('iDisplayStart');
        $sEcho = $this->getRequestParameter('sEcho');
        $limit = $this->getRequestParameter('iDisplayLength');
        $arr = array();

        /******   total records  *******/
        $c = new Criteria();
        $c->add(MlmDistributorPeer::UPLINE_DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $c->add(MlmDistributorPeer::PLACEMENT_TREE_LEVEL, null, Criteria::ISNULL);
        $c->add(MlmDistributorPeer::TREE_UPLINE_DIST_ID, null, Criteria::ISNULL);
        $c->add(MlmDistributorPeer::PLACEMENT_TREE_STRUCTURE, null, Criteria::ISNULL);
        $totalRecords = MlmDistributorPeer::doCount($c);

        /******   total filtered records  *******/
        if ($this->getRequestParameter('filterFullname') != "") {
            $c->addAnd(MlmDistributorPeer::FULL_NAME, "%" . $this->getRequestParameter('filterFullname') . "%", Criteria::LIKE);
        }
        if ($this->getRequestParameter('filterNickname') != "") {
            $c->addAnd(MlmDistributorPeer::NICKNAME, "%" . $this->getRequestParameter('filterNickname') . "%", Criteria::LIKE);
        }
        $totalFilteredRecords = MlmDistributorPeer::doCount($c);

        /******   sorting  *******/
        for ($i = 0; $i < intval($this->getRequestParameter('iSortingCols')); $i++)
        {
            if ($this->getRequestParameter('bSortable_' . intval($this->getRequestParameter('iSortCol_' . $i))) == "true") {
                if ("asc" == $this->getRequestParameter('sSortDir_' . $i)) {
                    $c->addAscendingOrderByColumn($aColumns[intval($this->getRequestParameter('iSortCol_' . $i))]);
                } else {
                    $c->addDescendingOrderByColumn($aColumns[intval($this->getRequestParameter('iSortCol_' . $i))]);
                }
            }
        }

        /******   pagination  *******/
        $pager = new sfPropelPager('MlmDistributor', $limit);
        $pager->setCriteria($c);
        $pager->setPage(($offset / $limit) + 1);
        $pager->init();

        foreach ($pager->getResults() as $result) {
            $arr[] = array(
                $result->getDistributorId() == null ? "" : $result->getDistributorId(),
                $result->getDistributorId() == null ? "" : $result->getDistributorId(),
                $result->getCreatedOn() == null ? "" : $result->getActiveDatetime(),
                $result->getDistributorCode() == null ? "" : $result->getDistributorCode(),
                $result->getFullName() == null ? "" : $result->getFullName(),
                $result->getNickname() == null ? "" : $result->getNickname(),
                $result->getIc() == null ? "" : $result->getIc(),
                $result->getRankCode() == null ? "" : $result->getRankCode(),
            );
        }
        $output = array(
            "sEcho" => intval($sEcho),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalFilteredRecords,
            "aaData" => $arr
        );
        echo json_encode($output);

        return sfView::HEADER_ONLY;
    }

    public function executeReloadTopup()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "REGISTRATION");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "RELOAD_MT4_FUND");

        $ledgerBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_MG);
        $this->ledgerBalance = $ledgerBalance;

        $distributorDB = MlmDistributorPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $this->distributorDB = $distributorDB;

        $mt4Amount = $this->getRequestParameter('mt4Amount');
        $pointNeeded = $this->getRequestParameter('mt4Amount');
        $mt4UserName = $this->getRequestParameter('mt4UserName', "");

        if ($mt4Amount > 0 && $this->getRequestParameter('transactionPassword') <> "") {
            $tbl_user = AppUserPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_USERID));

            if ($pointNeeded > $ledgerBalance) {
                $this->setFlash('errorMsg', "In-sufficient MG");

            } elseif (strtoupper($tbl_user->getUserpassword2()) <> strtoupper($this->getRequestParameter('transactionPassword'))) {
                $this->setFlash('errorMsg', "Invalid Security password");

            } elseif ($mt4UserName == "") {
                $this->setFlash('errorMsg', "Invalid MT4 ID.");

            } else {
                $tbl_account_ledger = new MlmAccountLedger();
                $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_MG);
                $tbl_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_TOPUP_MT4);
                $tbl_account_ledger->setCredit(0);
                $tbl_account_ledger->setDebit($pointNeeded);
                $tbl_account_ledger->setBalance($ledgerBalance - $pointNeeded);
                $tbl_account_ledger->setRemark("MT4 Fund :" . $mt4Amount);
                $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->save();

                $mlmMt4ReloadFund = new MlmMt4ReloadFund();
                $mlmMt4ReloadFund->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                $mlmMt4ReloadFund->setMt4UserName($mt4UserName);
                $mlmMt4ReloadFund->setAmount($this->getRequestParameter('mt4Amount'));
                $mlmMt4ReloadFund->setStatusCode(Globals::STATUS_PENDING);
                $mlmMt4ReloadFund->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlmMt4ReloadFund->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlmMt4ReloadFund->save();

                $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Your MT4 Fund Reload has been submitted."));

                return $this->redirect('/member/reloadTopup');
            }
        }
    }

    public function executeTransferEcash()
    {
        $ledgerAccountBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);
        $this->ledgerAccountBalance = $ledgerAccountBalance;

        $processFee = 0;
        /*$c = new Criteria();
        $c->add(AppSettingPeer::SETTING_PARAMETER, Globals::SETTING_TRANSFER_PROCESS_FEE);
        $settingDB = AppSettingPeer::doSelectOne($c);
        if ($settingDB) {
            $processFee = $settingDB->getSettingValue();
        }*/
        $this->processFee = $processFee;

        if ($this->getRequestParameter('sponsorId') <> "" && $this->getRequestParameter('ecashAmount') > 0 && $this->getRequestParameter('transactionPassword') <> "") {
            $appUser = AppUserPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_USERID));

            $sponsorId = $this->getRequestParameter('sponsorId');

            $c = new Criteria();
            $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $sponsorId);
            $existDist = MlmDistributorPeer::doSelectOne($c);

            $array = explode(',', Globals::STATUS_ACTIVE . "," . Globals::STATUS_PENDING);
            $c = new Criteria();
            $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $sponsorId);
            $c->add(MlmDistributorPeer::PLACEMENT_TREE_STRUCTURE, "%|" . $this->getUser()->getAttribute(Globals::SESSION_DISTID) . "|%", Criteria::LIKE);
            $c->add(MlmDistributorPeer::STATUS_CODE, $array, Criteria::IN);
            $existUser = MlmDistributorPeer::doSelectOne($c);

            if (!$existUser && $existDist) {
                $c = new Criteria();
                $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $this->getUser()->getAttribute(Globals::SESSION_USERNAME));
                $c->add(MlmDistributorPeer::PLACEMENT_TREE_STRUCTURE, "%|" . $existDist->getDistributorId() . "|%", Criteria::LIKE);
                $c->add(MlmDistributorPeer::STATUS_CODE, $array, Criteria::IN);
                $existUser = MlmDistributorPeer::doSelectOne($c);

                if (!$existUser) {
                    $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Invalid User ID."));
                    return $this->redirect('/member/transferEcash');
                }
            }

            if (($this->getRequestParameter('ecashAmount') + $processFee) > $ledgerAccountBalance) {

                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("In-sufficient CP2 Amount"));
                return $this->redirect('/member/transferEcash');
            } elseif (strtoupper($appUser->getUserPassword2()) <> strtoupper($this->getRequestParameter('transactionPassword'))) {

                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Invalid Security password"));
                return $this->redirect('/member/transferEcash');
            } elseif (strtoupper($this->getRequestParameter('sponsorId')) == $this->getUser()->getAttribute(Globals::SESSION_USERNAME)) {

                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("You are not allow to transfer to own account."));
                return $this->redirect('/member/transferEcash');
            } elseif ($this->getRequestParameter('sponsorId') <> "" && $this->getRequestParameter('ecashAmount') > 0) {

                $ecashBalance = $this->getAccountBalance($existDist->getDistributorId(), Globals::ACCOUNT_TYPE_ECASH);

                $toId = $existDist->getDistributorId();
                $toCode = $existDist->getDistributorCode();
                $toName = $existDist->getNickname();
                $toBalance = $ecashBalance;
                $fromId = $this->getUser()->getAttribute(Globals::SESSION_DISTID);
                $fromCode = $this->getUser()->getAttribute(Globals::SESSION_USERNAME);
                $fromName = $this->getUser()->getAttribute(Globals::SESSION_NICKNAME);
                $fromBalance = $ledgerAccountBalance;

                $mlm_account_ledger = new MlmAccountLedger();
                $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
                $mlm_account_ledger->setDistId($fromId);
                $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_TRANSFER_TO);
                $mlm_account_ledger->setRemark(Globals::ACCOUNT_LEDGER_ACTION_TRANSFER_TO . " " . $toCode . " (" . $toName . ")");
                $mlm_account_ledger->setCredit(0);
                $mlm_account_ledger->setDebit($this->getRequestParameter('ecashAmount'));
                $mlm_account_ledger->setBalance($fromBalance - $this->getRequestParameter('ecashAmount'));
                $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_account_ledger->save();

                //$this->revalidateAccount($fromId, Globals::ACCOUNT_TYPE_ECASH);

                $tbl_account_ledger = new MlmAccountLedger();
                $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
                $tbl_account_ledger->setDistId($toId);
                $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_TRANSFER_FROM);
                $tbl_account_ledger->setRemark(Globals::ACCOUNT_LEDGER_ACTION_TRANSFER_FROM . " " . $fromCode . " (" . $fromName . ")");
                $tbl_account_ledger->setCredit($this->getRequestParameter('ecashAmount'));
                $tbl_account_ledger->setDebit(0);
                $tbl_account_ledger->setBalance($toBalance + $this->getRequestParameter('ecashAmount'));
                $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->save();

                //$this->revalidateAccount($toId, Globals::ACCOUNT_TYPE_ECASH);

                // ******       processing fees      ****************
                /*$tbl_account_ledger = new MlmAccountLedger();
                $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
                $tbl_account_ledger->setDistId($fromId);
                $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_PROCESS_CHARGE);
                $tbl_account_ledger->setRemark(Globals::ACCOUNT_LEDGER_ACTION_TRANSFER_TO . " " . $toCode . " (" . $toName . ") PROCESS CHARGES");
                $tbl_account_ledger->setCredit(0);
                $tbl_account_ledger->setDebit($processFee);
                $tbl_account_ledger->setBalance($fromBalance - ($this->getRequestParameter('ecashAmount') + $processFee));
                $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->save();

                $this->revalidateAccount($fromId, Globals::ACCOUNT_TYPE_ECASH);*/

                // ******       company account      ****************
                /*$c = new Criteria();
                $c->add(MlmAccountPeer::ACCOUNT_TYPE, Globals::ACCOUNT_TYPE_ECASH);
                $c->addAnd(MlmAccountPeer::DIST_ID, Globals::SYSTEM_COMPANY_DIST_ID);
                $companyAccount = MlmAccountPeer::doSelectOne($c);

                $tbl_account_ledger = new MlmAccountLedger();
                $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
                $tbl_account_ledger->setDistId(Globals::SYSTEM_COMPANY_DIST_ID);
                $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_TRANSFER);
                $tbl_account_ledger->setRemark(Globals::ACCOUNT_LEDGER_ACTION_PROCESS_CHARGE . " " . $fromCode . " -> " . $toCode);
                $tbl_account_ledger->setCredit($processFee);
                $tbl_account_ledger->setDebit(0);
                $tbl_account_ledger->setBalance($companyAccount->getBalance() + $processFee);
                $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->save();

                $this->revalidateAccount(Globals::SYSTEM_COMPANY_DIST_ID, Globals::ACCOUNT_TYPE_ECASH);*/

                $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Transfer success"));

                return $this->redirect('/member/transferEcash');
            }
        }
    }

    public function executeTransferCp1()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "MY_ACCOUNT");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "TRANSFER_CP1");

        $ledgerAccountBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_EPOINT);
        $this->ledgerAccountBalance = $ledgerAccountBalance;

        $processFee = 0;
        /*$c = new Criteria();
        $c->add(AppSettingPeer::SETTING_PARAMETER, Globals::SETTING_TRANSFER_PROCESS_FEE);
        $settingDB = AppSettingPeer::doSelectOne($c);
        if ($settingDB) {
            $processFee = $settingDB->getSettingValue();
        }*/
        $this->processFee = $processFee;

        if ($this->getRequestParameter('sponsorId') <> "" && $this->getRequestParameter('epointAmount') > 0 && $this->getRequestParameter('transactionPassword') <> "") {
            $appUser = AppUserPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_USERID));

            $sponsorId = $this->getRequestParameter('sponsorId');

            $c = new Criteria();
            $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $sponsorId);
            $existDist = MlmDistributorPeer::doSelectOne($c);

            $array = explode(',', Globals::STATUS_ACTIVE . "," . Globals::STATUS_PENDING);
            $c = new Criteria();
            $c1 = $c->getNewCriterion(MlmDistributorPeer::DISTRIBUTOR_CODE, $sponsorId);
            $c2 = $c->getNewCriterion(MlmDistributorPeer::NICKNAME, $sponsorId);
            $c1->addOr($c2);
            $c->add($c1);
            //$c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $sponsorId);
            $c->add(MlmDistributorPeer::PLACEMENT_TREE_STRUCTURE, "%|" . $this->getUser()->getAttribute(Globals::SESSION_DISTID) . "|%", Criteria::LIKE);
            $c->add(MlmDistributorPeer::STATUS_CODE, $array, Criteria::IN);
            $existUser = MlmDistributorPeer::doSelectOne($c);

            if (!$existUser && $existDist) {
                $c = new Criteria();
                $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $this->getUser()->getAttribute(Globals::SESSION_USERNAME));
                $c->add(MlmDistributorPeer::PLACEMENT_TREE_STRUCTURE, "%|" . $existDist->getDistributorId() . "|%", Criteria::LIKE);
                $c->add(MlmDistributorPeer::STATUS_CODE, $array, Criteria::IN);
                $existUser = MlmDistributorPeer::doSelectOne($c);

                if (!$existUser) {
                    $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Invalid User ID."));
                    return $this->redirect('/member/transferCp1');
                }
            }

            if (($this->getRequestParameter('epointAmount') + $processFee) > $ledgerAccountBalance) {

                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("In-sufficient CP1"));
                return $this->redirect('/member/transferCp1');

            } elseif (strtoupper($appUser->getUserPassword2()) <> strtoupper($this->getRequestParameter('transactionPassword'))) {

                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Invalid Security password"));
                return $this->redirect('/member/transferCp1');

            } elseif (strtoupper($this->getRequestParameter('sponsorId')) == $this->getUser()->getAttribute(Globals::SESSION_USERNAME)) {

                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("You are not allow to transfer to own account."));
                return $this->redirect('/member/transferCp1');

            } elseif ($this->getRequestParameter('sponsorId') <> "" && $this->getRequestParameter('epointAmount') > 0) {
                $epointBalance = $this->getAccountBalance($existDist->getDistributorId(), Globals::ACCOUNT_TYPE_EPOINT);

                $toId = $existDist->getDistributorId();
                $toCode = $existDist->getDistributorCode();
                $toName = $existDist->getNickname();
                $toBalance = $epointBalance;
                $fromId = $this->getUser()->getAttribute(Globals::SESSION_DISTID);
                $fromCode = $this->getUser()->getAttribute(Globals::SESSION_USERNAME);
                $fromName = $this->getUser()->getAttribute(Globals::SESSION_NICKNAME);
                $fromBalance = $ledgerAccountBalance;

                $mlm_account_ledger = new MlmAccountLedger();
                $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_EPOINT);
                $mlm_account_ledger->setDistId($fromId);
                $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_TRANSFER_TO);
                $mlm_account_ledger->setRemark(Globals::ACCOUNT_LEDGER_ACTION_TRANSFER_TO . " " . $toCode);
                $mlm_account_ledger->setCredit(0);
                $mlm_account_ledger->setDebit($this->getRequestParameter('epointAmount'));
                $mlm_account_ledger->setBalance($fromBalance - $this->getRequestParameter('epointAmount'));
                $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_account_ledger->save();

                //$this->revalidateAccount($fromId, Globals::ACCOUNT_TYPE_EPOINT);

                $tbl_account_ledger = new MlmAccountLedger();
                $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_EPOINT);
                $tbl_account_ledger->setDistId($toId);
                $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_TRANSFER_FROM);
                $tbl_account_ledger->setRemark(Globals::ACCOUNT_LEDGER_ACTION_TRANSFER_FROM . " " . $fromCode . " (" . $fromName . ")");
                $tbl_account_ledger->setCredit($this->getRequestParameter('epointAmount'));
                $tbl_account_ledger->setDebit(0);
                $tbl_account_ledger->setBalance($toBalance + $this->getRequestParameter('epointAmount'));
                $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->save();

                //$this->revalidateAccount($toId, Globals::ACCOUNT_TYPE_EPOINT);

                // ******       processing fees      ****************
                /*$tbl_account_ledger = new MlmAccountLedger();
                $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
                $tbl_account_ledger->setDistId($fromId);
                $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_PROCESS_CHARGE);
                $tbl_account_ledger->setRemark(Globals::ACCOUNT_LEDGER_ACTION_TRANSFER_TO . " " . $toCode . " (" . $toName . ") PROCESS CHARGES");
                $tbl_account_ledger->setCredit(0);
                $tbl_account_ledger->setDebit($processFee);
                $tbl_account_ledger->setBalance($fromBalance - ($this->getRequestParameter('ecashAmount') + $processFee));
                $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->save();

                $this->revalidateAccount($fromId, Globals::ACCOUNT_TYPE_ECASH);*/

                // ******       company account      ****************
                /*$c = new Criteria();
                $c->add(MlmAccountPeer::ACCOUNT_TYPE, Globals::ACCOUNT_TYPE_EPOINT);
                $c->addAnd(MlmAccountPeer::DIST_ID, Globals::SYSTEM_COMPANY_DIST_ID);
                $companyAccount = MlmAccountPeer::doSelectOne($c);

                $tbl_account_ledger = new MlmAccountLedger();
                $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_EPOINT);
                $tbl_account_ledger->setDistId(Globals::SYSTEM_COMPANY_DIST_ID);
                $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_TRANSFER);
                $tbl_account_ledger->setRemark(Globals::ACCOUNT_LEDGER_ACTION_PROCESS_CHARGE . " " . $fromCode . " -> " . $toCode);
                $tbl_account_ledger->setCredit($processFee);
                $tbl_account_ledger->setDebit(0);
                $tbl_account_ledger->setBalance($companyAccount->getBalance() + $processFee);
                $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->save();

                $this->revalidateAccount(Globals::SYSTEM_COMPANY_DIST_ID, Globals::ACCOUNT_TYPE_EPOINT);*/

                $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Transfer success"));

                return $this->redirect('/member/transferCp1');
            }
        }
    }

    public function executeMgLog()
    {
        if ($this->getUser()->getAttribute(Globals::SESSION_SECURITY_PASSWORD_REQUIRED_WALLET, false) == false) {
            return $this->redirect('/member/securityPasswordRequired?doAction=W');
        }

        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "MY_STATEMENT");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "MG_LOG");
    }

    public function executeMpLog()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "MY_STATEMENT");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "MP_LOG");
    }

    public function executeMt4Log()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "MY_STATEMENT");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "MT4_LOG");
    }

    public function executeMaintenanceLog()
    {
    }

    public function executeFetchAnnouncementById()
    {
        $arr = "";
        if ($this->getRequestParameter('announcementId') <> "") {
            $announcement = MlmAnnouncementPeer::retrieveByPk($this->getRequestParameter('announcementId'));

            if ($announcement) {
                $title = $announcement->getTitle();
                $content = $announcement->getContent();
                if ($this->getUser()->getCulture() == 'cn') {
                    $title = $announcement->getTitleCn();
                    $content = $announcement->getContentCn();
                }
                $arr = array(
                    'title' => $title,
                    'content' => $content
                );
            }
        }

        echo json_encode($arr);
        return sfView::HEADER_ONLY;
    }

    public function executeLoginPassword()
    {
        if ($this->getRequestParameter('oldPassword')) {
            $c = new Criteria();
            $c->add(AppUserPeer::USER_ID, $this->getUser()->getAttribute(Globals::SESSION_USERID));
            $c->add(AppUserPeer::USERPASSWORD2, $this->getRequestParameter('changePasswordSecurityPassword'));
            $exist = AppUserPeer::doSelectOne($c);

            if (!$exist) {
                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Security password is not valid."));
                return $this->redirect('/member/viewProfile');
            }

            $c = new Criteria();
            $c->add(AppUserPeer::USER_ID, $this->getUser()->getAttribute(Globals::SESSION_USERID));
            $c->add(AppUserPeer::USERPASSWORD, $this->getRequestParameter('oldPassword'));
            $exist = AppUserPeer::doSelectOne($c);

            if (!$exist) {
                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Old password is not valid."));
            } else {
                $exist->setUserpassword($this->getRequestParameter('newPassword'));
                //$exist->setKeepPassword($this->getRequestParameter('newPassword'));
                $exist->save();
                $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Password updated"));
            }
            //return $this->redirect('/member/loginPassword');
        }
        return $this->redirect('/member/viewProfile');
    }

    public function executeTransactionPassword()
    {
        if ($this->getRequestParameter('oldSecurityPassword')) {
            $c = new Criteria();
            $c->add(AppUserPeer::USER_ID, $this->getUser()->getAttribute(Globals::SESSION_USERID));
            $c->add(AppUserPeer::USERPASSWORD2, $this->getRequestParameter('oldSecurityPassword'));
            $exist = AppUserPeer::doSelectOne($c);

            if (!$exist) {
                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Old Security password is not valid."));
            } else {
                $exist->setUserpassword2($this->getRequestParameter('newSecurityPassword'));
                //$exist->setKeepPassword2($this->getRequestParameter('newSecurityPassword'));
                $exist->save();
                $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Security Password updated"));
            }
            //return $this->redirect('/member/transactionPassword');
        }
        return $this->redirect('/member/viewProfile');
    }

    public function executeAnnouncement()
    {
        $announcement = MlmAnnouncementPeer::retrieveByPK($this->getRequestParameter('id'));
        $this->forward404Unless($announcement);

        $this->announcement = $announcement;
    }

    public function executeSponsorTree()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "BUSINESS_NETWORK");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "SPONSOR_GENEALOGY");

        if ($this->getUser()->getAttribute(Globals::SESSION_SECURITY_PASSWORD_REQUIRED_GENEALOGY, false) == false) {
            return $this->redirect('/member/securityPasswordRequired?doAction=G');
        }

        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "BUSINESS_NETWORK");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "SPONSOR_GENEALOGY");

        $id = $this->getUser()->getAttribute(Globals::SESSION_DISTID);
        $distinfo = MlmDistributorPeer::retrieveByPk($id);
        $this->distinfo = $distinfo;
        $this->hasChild = $this->checkHasChild($distinfo->getDistributorId());

        /*********************/
        /* Search Function
         * ********************/
        $fullName = $this->getRequestParameter('fullName');
        $arrTree = array();

        if ($fullName != "") {
            $c = new Criteria();
            $c1 = $c->getNewCriterion(MlmDistributorPeer::DISTRIBUTOR_CODE, $fullName);
            $c2 = $c->getNewCriterion(MlmDistributorPeer::NICKNAME, $fullName);
            $c1->addOr($c2);
            $c->add($c1);

            $c->add(MlmDistributorPeer::TREE_STRUCTURE, "%|" . $this->getUser()->getAttribute(Globals::SESSION_DISTID) . "|%", Criteria::LIKE);
            $c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
            $distinfo = MlmDistributorPeer::doSelectOne($c);

            if (!$distinfo) {
                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Member is not exist."));
                return $this->redirect('/member/sponsorTree');
            }

            $this->distinfo = $distinfo;
            $this->hasChild = $this->checkHasChild($distinfo->getDistributorId());
        }

        $color = "blue";
        $packageName = "";

        $package = MlmPackagePeer::retrieveByPK($distinfo->getRankId());
        if ($package) {
            $color = $package->getColor();
            $packageName = $package->getPackageName();
        }
        if ($distinfo->getStatusCode() != Globals::STATUS_ACTIVE) {
            $headColor = "black";
        }
        $this->headColor = $color;
        $this->arrTree = $arrTree;
        $this->fullName = $fullName;
        $this->packageName = $packageName;

        $c = new Criteria();
        //$c->add(MlmPackagePeer::PUBLIC_PURCHASE, 1);
        $this->packageDBs = MlmPackagePeer::doSelect($c);
    }

    public function executeManipulateSponsorTree()
    {
        $parentId = $this->getRequestParameter('root');
        $arrTree = array();
        $html = "";
        if ($parentId != "") {
            $c = new Criteria();
            $c->add(MlmDistributorPeer::UPLINE_DIST_ID, $parentId);
            //$c->addAnd(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
            $dists = MlmDistributorPeer::doSelect($c);

            $idx = 0;
            $count = count($dists);
            foreach ($dists as $dist)
            {
                $idx++;
                $hasChild = $this->checkHasChild($dist->getDistributorId());

                $treeLine = "tree-controller-lplus-line";
                $treeLine2 = "tree-controller-lplus-right";
                $treeLineNoChild = "tree-controller-t-line";
                $treeLineNoChild2 = "tree-controller-t-right";
                $treeControllerWrap = "tree-controller-wrap";
                $img = "<img class='tree-plus-button' src='/css/network/plus.png'>";
                if ($idx == $count) {
                    $treeLineNoChild = "tree-controller-l-line";
                    $treeLineNoChild2 = "tree-controller-l-right";
                    $treeControllerWrap = "tree-controller-l-wrap";
                }

                if ($hasChild) {
                } else {
                    $img = "";
                    $treeLine = $treeLineNoChild;
                    $treeLine2 = $treeLineNoChild2;
                }

                $headColor = "blue";
                $packageName = "";

                $package = MlmPackagePeer::retrieveByPK($dist->getRankId());
                if ($package) {
                    $headColor = $package->getColor();
                    $packageName = $package->getPackageName();
                }
                if ($dist->getStatusCode() != Globals::STATUS_ACTIVE) {
                    $headColor = "black";
                }
                $html .= "<div class='" . $treeControllerWrap . "'>
                        <div class='controller-node-con'>
                            <div class='tree-controller " . $treeLine . "'>
                                <div class='tree-controller-in " . $treeLine2 . "'>
                                    " . $img . "
                                </div>
                            </div>
                            <div class='node-info-raw' id='node-id-" . $dist->getDistributorId() . "'>
                                <div class='node-info'>
                                    <span class='user-rank'><img
                                            src='/css/network/" . $headColor . "_head.png'></span>
                                    <span class='user-id'>" . $dist->getDistributorCode() . " (" . $dist->getNickName() . ")</span>
                                    <span class='user-joined'>" . $this->getContext()->getI18N()->__("Joined") . " " . date('Y-m-d', strtotime($dist->getActiveDatetime())) . "</span>
                                </div>
                            </div>
                        </div>";
                if ($hasChild) {
                    $html .= "<div id='node-wrapper-" . $dist->getDistributorId() . "' class='ajax-more'></div>";
                }
                $html .= "</div>";
            }
        }


        //echo json_encode($arrTree);
        echo $html;
        return sfView::HEADER_ONLY;
    }

    public function executeMgWithdrawal()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "WITHDRAWAL");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "MG_WITHDRAWAL");

        $ledgerAccountBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_MG);
        $this->ledgerAccountBalance = $ledgerAccountBalance;

        $withdrawAmount = $this->getRequestParameter('ecashAmount') * 1.05;
        //$processFee = 0;
        $processFee = $this->getRequestParameter('ecashAmount') * 5 / 100;

        if ($this->getRequestParameter('ecashAmount') > 0 && $this->getRequestParameter('transactionPassword') <> "") {
            $tbl_user = AppUserPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_USERID));

            if ($withdrawAmount > $ledgerAccountBalance) {
                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("In-sufficient MG"));

            } elseif (strtoupper($tbl_user->getUserpassword2()) <> strtoupper($this->getRequestParameter('transactionPassword'))) {
                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Invalid Security password"));

            } elseif ($withdrawAmount > 0) {
                $tbl_account_ledger = new MlmAccountLedger();
                $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_MG);
                $tbl_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_WITHDRAWAL);
                $tbl_account_ledger->setCredit(0);
                $tbl_account_ledger->setDebit($withdrawAmount);
                $tbl_account_ledger->setBalance($ledgerAccountBalance - $withdrawAmount);
                $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->save();

                // ******       company account      ****************
                $companyEcashBalance = $this->getAccountBalance(Globals::SYSTEM_COMPANY_DIST_ID, Globals::ACCOUNT_TYPE_MG);

                $tbl_account_ledger = new MlmAccountLedger();
                $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_MG);
                $tbl_account_ledger->setDistId(Globals::SYSTEM_COMPANY_DIST_ID);
                $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_WITHDRAWAL);
                $tbl_account_ledger->setRemark(Globals::ACCOUNT_LEDGER_ACTION_WITHDRAWAL . " " . $this->getUser()->getAttribute(Globals::SESSION_USERNAME));
                $tbl_account_ledger->setCredit($processFee);
                $tbl_account_ledger->setDebit(0);
                $tbl_account_ledger->setBalance($companyEcashBalance + $processFee);
                $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->save();

                $tbl_ecash_withdraw = new MlmEcashWithdraw();
                $tbl_ecash_withdraw->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                $tbl_ecash_withdraw->setDeduct($withdrawAmount);
                $tbl_ecash_withdraw->setAmount($withdrawAmount - $processFee);
                $tbl_ecash_withdraw->setStatusCode(Globals::WITHDRAWAL_PENDING);
                $tbl_ecash_withdraw->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_ecash_withdraw->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_ecash_withdraw->save();

                $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Your MG withdrawal has been submitted."));

                return $this->redirect('/member/mgWithdrawal');
            }
        }
    }

    public function executeMt4Withdrawal()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "WITHDRAWAL");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "MT4_WITHDRAWAL");

        $usdToMyr = 1;
        $handlingCharge = 4;
        $handlingChargeInUsd = 40;

        /*$c = new Criteria();
        $c->add(AppSettingPeer::SETTING_PARAMETER, Globals::SETTING_USD_TO_MYR);
        $settingDB = AppSettingPeer::doSelectOne($c);
        if ($settingDB) {
            $usdToMyr = $settingDB->getSettingValue();
        }*/
        $this->usdToMyr = $usdToMyr;

        $c = new Criteria();
        $c->add(AppSettingPeer::SETTING_PARAMETER, Globals::SETTING_MT4_HANDLING_FEE);
        $settingDB = AppSettingPeer::doSelectOne($c);
        if ($settingDB) {
            $handlingCharge = $settingDB->getSettingValue();
        }
        $this->handlingCharge = $handlingCharge;

        $c = new Criteria();
        $c->add(AppSettingPeer::SETTING_PARAMETER, Globals::SETTING_MT4_HANDLING_FEE_USD);
        $settingDB = AppSettingPeer::doSelectOne($c);
        if ($settingDB) {
            $handlingChargeInUsd = $settingDB->getSettingValue();
        }
        $this->handlingChargeInUsd = $handlingChargeInUsd;

        $distributorDB = MlmDistributorPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $this->distributorDB = $distributorDB;

        if ($this->getRequestParameter('mt4Amount') > 0 && $this->getRequestParameter('transactionPassword') <> "" && $this->getRequestParameter('paymentType') <> "") {
            $tbl_user = AppUserPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_USERID));

            if (strtoupper($tbl_user->getUserpassword2()) <> strtoupper($this->getRequestParameter('transactionPassword'))) {
                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Invalid Security password"));

            } else if (!$this->getRequestParameter('mt4Id')) {
                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Invalid MT4 ID."));

            } else {
                $paymentType = $this->getRequestParameter('paymentType');
                $usdAmount = $this->getRequestParameter('mt4Amount');
                $mt4Id = $this->getRequestParameter('mt4Id');

                $mt4Withdraw = new MlmMt4Withdraw();
                $mt4Withdraw->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                $mt4Withdraw->setMt4UserName($mt4Id);
                $mt4Withdraw->setStatusCode(Globals::WITHDRAWAL_PENDING);

                $minHandlingFee = 0;
                $myrAmount = 0;
                $handlingFee = 0;
                $currencyCode = "USD";
                $grandAmount = 0;

                /*if ($paymentType == "BANK") {
                    $currencyCode = "MYR";

                    $usdCurrency = $usdToMyr;
                    $minHandlingFee = $handlingChargeInUsd * $usdCurrency;
                    $myrAmount = $usdAmount * $usdCurrency;
                    $handlingFee = floor($myrAmount * $usdCurrency / 100);
                    if ($handlingFee < $minHandlingFee)
                        $handlingFee = $minHandlingFee;

                    $grandAmount = $myrAmount - $handlingFee;

                    $mt4Withdraw->setAmountRequested($myrAmount);
                    $mt4Withdraw->setRemarks("MT4 Fund :".$usdAmount);
                } elseif ($paymentType == "VISA") {*/
                $currencyCode = "USD";

                $minHandlingFee = floor($usdAmount * $handlingCharge / 100);
                if ($minHandlingFee < $handlingChargeInUsd)
                    $handlingFee = $handlingChargeInUsd;
                else
                    $handlingFee = $minHandlingFee;

                $grandAmount = $usdAmount - $handlingFee;

                $mt4Withdraw->setAmountRequested($usdAmount);
                //}

                $mt4Withdraw->setHandlingFee($handlingFee);
                $mt4Withdraw->setGrandAmount($grandAmount);
                $mt4Withdraw->setPaymentType($paymentType);
                $mt4Withdraw->setCurrencyCode($currencyCode);

                $mt4Withdraw->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mt4Withdraw->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mt4Withdraw->save();

                $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Your MT4 withdrawal has been submitted."));

                return $this->redirect('/member/mt4Withdrawal');
            }
        }
    }

    public function executeBonusDetails()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "COMMISSION");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "COMMISSION");

        $distDB = MlmDistributorPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $this->forward404Unless($distDB);

        $joinDate = $distDB->getActiveDatetime();

        $dsb = $this->getCommissionBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::COMMISSION_TYPE_DRB);
        $pipsBonus = $this->getCommissionBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::COMMISSION_TYPE_PIPS_BONUS);
        $creditRefunds = $this->getCommissionBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::COMMISSION_TYPE_CREDIT_REFUND);
        $fundManagements = $this->getCommissionBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::COMMISSION_TYPE_FUND_MANAGEMENT);
        $pairingBonus = $this->getCommissionBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::COMMISSION_TYPE_GDB);
        $specialBonus = 0;
        $totalLotTraded = 0;

        $this->dsb = number_format($dsb, 2);
        $this->pipsBonus = number_format($pipsBonus, 2);
        $this->creditRefund = number_format($creditRefunds, 2);
        $this->fundManagement = number_format($fundManagements, 2);
        $this->pairingBonus = number_format($pairingBonus, 2);
        $this->specialBonus = number_format($specialBonus, 2);
        $this->totalLotTraded = number_format($totalLotTraded, 2);

        $this->total = number_format($dsb + $pipsBonus + $creditRefunds + $fundManagements + $pairingBonus + $specialBonus, 2);

        /* *************************
         *  PIPS DETAIL
         * **************************/
        $currentMonth = date('m');
        $currentYear = date('Y');

        $anode = array();

        $idx = 0;
        if ($joinDate != null) {
            $joinMonth = date('m', strtotime($joinDate));
            $joinYear = date('Y', strtotime($joinDate));
            for ($x = intval($joinYear); $x <= intval($currentYear); $x++) {
                if ($x != $currentYear) {
                    for ($i = intval($joinMonth); $i <= 12; $i++) {
                        $anode[$idx]["year"] = $x;
                        $anode[$idx]["month"] = $i;
                        $anode[$idx]["rb_bonus"] = $this->getRbDetailByMonth($distDB->getDistributorId(), $i, $x);
                        $anode[$idx]["paring_bonus"] = $this->getPairingDetailByMonth($distDB->getDistributorId(), $i, $x);
                        $idx++;
                    }
                } else {
                    if ($joinYear != $currentYear) {
                        $joinMonth = 1;
                    }
                    for ($i = intval($joinMonth); $i <= intval($currentMonth); $i++) {
                        $anode[$idx]["year"] = $x;
                        $anode[$idx]["month"] = $i;
                        $anode[$idx]["rb_bonus"] = $this->getRbDetailByMonth($distDB->getDistributorId(), $i, $x);
                        $anode[$idx]["paring_bonus"] = $this->getPairingDetailByMonth($distDB->getDistributorId(), $i, $x);
                        $idx++;
                    }
                }
            }
        }
        $this->anode = $anode;
    }

    public function executeProfile()
    {
    }

    public function executeRegistration()
    {
    }

    public function executeVerifyNickName()
    {
        $c = new Criteria();
        $c->add(MlmDistributorPeer::NICKNAME, $this->getRequestParameter('nickName'));
        $exist = MlmDistributorPeer::doSelectOne($c);

        if ($exist) {
            echo 'false';
        } else {
            echo 'true';
        }

        return sfView::HEADER_ONLY;
    }

    public function executeVerifyUserName()
    {
        $c = new Criteria();
        $c->add(AppUserPeer::USERNAME, $this->getRequestParameter('userName'));
        $exist = AppUserPeer::doSelectOne($c);

        if ($exist) {
            echo 'false';
        } else {
            echo 'true';
        }

        return sfView::HEADER_ONLY;
    }

    public function executeVerifyFullName()
    {
        $c = new Criteria();
        $c->add(MlmDistributorPeer::FULL_NAME, $this->getRequestParameter('fullname'));
        $exist = MlmDistributorPeer::doSelectOne($c);

        if ($exist) {
            echo 'false';
        } else {
            echo 'true';
        }

        return sfView::HEADER_ONLY;
    }

    public function executeViewProfile()
    {
        if ($this->getUser()->getAttribute(Globals::SESSION_SECURITY_PASSWORD_REQUIRED_VIEW_PROFILE, false) == false) {
            return $this->redirect('/member/securityPasswordRequired?doAction=VP');
        }

        $distDB = MlmDistributorPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $this->forward404Unless($distDB);

        $c = new Criteria();
        $c->add(MlmDistributorPeer::DISTRIBUTOR_ID, $distDB->getUplineDistId());
        $sponsor = MlmDistributorPeer::doSelectOne($c);
        if (!$sponsor) {
            $sponsor = new MlmDistributor();
        }

        $this->sponsor = $sponsor;
        $this->distDB = $distDB;
    }

    public function executeBankInformation()
    {
        $distDB = MlmDistributorPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $this->forward404Unless($distDB);

        $this->distDB = $distDB;

        $this->bankName2 = $this->getAppSetting(Globals::SETTING_BANK_NAME_2);
        $this->bankSwiftCode2 = $this->getAppSetting(Globals::SETTING_BANK_SWIFT_CODE_2);
        $this->iban2 = $this->getAppSetting(Globals::SETTING_IBAN_2);
        $this->bankAccountHolder2 = $this->getAppSetting(Globals::SETTING_BANK_ACCOUNT_HOLDER_2);
        $this->bankAccountNumber2 = $this->getAppSetting(Globals::SETTING_BANK_ACCOUNT_NUMBER_2);
        $this->cityOfBank2 = $this->getAppSetting(Globals::SETTING_CITY_OF_BANK_2);
        $this->countryOfBank2 = $this->getAppSetting(Globals::SETTING_COUNTRY_OF_BANK_2);

        $this->bankName = $this->getAppSetting(Globals::SETTING_BANK_NAME);
        $this->bankSwiftCode = $this->getAppSetting(Globals::SETTING_BANK_SWIFT_CODE);
        $this->iban = $this->getAppSetting(Globals::SETTING_IBAN);
        $this->bankAccountHolder = $this->getAppSetting(Globals::SETTING_BANK_ACCOUNT_HOLDER);
        $this->bankAccountNumber = $this->getAppSetting(Globals::SETTING_BANK_ACCOUNT_NUMBER);
        $this->cityOfBank = $this->getAppSetting(Globals::SETTING_CITY_OF_BANK);
        $this->countryOfBank = $this->getAppSetting(Globals::SETTING_COUNTRY_OF_BANK);
    }

    public function executeDailyBonus()
    {
        $con = Propel::getConnection(MlmDailyBonusLogPeer::DATABASE_NAME);
        try {
            $con->begin();
            $this->getUser()->setCulture("cn");
            print_r("Start<br>");
            /*$c = new Criteria();
            $c->add(MlmDailyBonusLogPeer::BONUS_TYPE, Globals::DAILY_BONUS_LOG_TYPE_DAILY);
            $c->addDescendingOrderByColumn(MlmDailyBonusLogPeer::BONUS_DATE);
            $mlmDailyBonusLogDB = MlmDailyBonusLogPeer::doSelectOne($c);
            print_r("Fetch Daily Bonus Log<br>");

            $dateUtil = new DateUtil();
            $currentDate = $dateUtil->formatDate("Y-m-d", date("Y-m-d"));
            print_r("currentDate=".$currentDate."<br>");

            if ($mlmDailyBonusLogDB) {
                $bonusDate = $dateUtil->formatDate("Y-m-d", $mlmDailyBonusLogDB->getBonusDate());
                print_r("bonusDate=".$bonusDate."<br>");*/

            //$level = 0;
            //while ($level < 10) {
            //print_r("level start ".$level."<br><br>");
            //if ($bonusDate == $currentDate) {
            //print_r("break<br>");
            //break;
            //}

            $c = new Criteria();
            $c->add(MlmDistInvestmentPackageBatchPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
            $c->add(MlmDistInvestmentPackageBatchPeer::BONUS_DATE, date("Y-m-d H:i:s"), Criteria::LESS_THAN);
            $mlm_dist_investment_package_batch = MlmDistInvestmentPackageBatchPeer::doSelectOne($c);

            $dateUtil = new DateUtil();
            $date = $dateUtil->formatDate("Y-m-d", $dateUtil->addDate(date("Y-m-d"), Globals::BONUS_CALCULATION_DAY, 0, 0));

            if ($mlm_dist_investment_package_batch) {
                $mlm_dist_investment_package_batch_new = new MlmDistInvestmentPackageBatch();
                $mlm_dist_investment_package_batch_new->setTotalPackage(0);
                $mlm_dist_investment_package_batch_new->setBonusDate($date);
                $mlm_dist_investment_package_batch_new->setStatusCode(Globals::STATUS_ACTIVE);
                $mlm_dist_investment_package_batch_new->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_dist_investment_package_batch_new->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_dist_investment_package_batch_new->save();

                $mlm_dist_investment_package_batch->setStatusCode(Globals::STATUS_PROCESSING);
                $mlm_dist_investment_package_batch->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_dist_investment_package_batch->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_dist_investment_package_batch->save();

                //$totalInvestmentPackageSold = $this->getTotalCurrentInvestmentPackageSold();
                $totalPackageSoldFromSystem = $this->getTotalInvestmentPackageSold(null);

                $totalPackageInvested = $this->toFreezeAndCalculateInvestmentPackage($mlm_dist_investment_package_batch->getBatchId());
                // 1000 * 0.9 (10 percent give to system maintenenace) * 0.9 (direct sponsor payout) * $totalPackageInvested / $totalPackageSoldFromSystem
                $eachInvestmentPackageFundReturn = (Globals::INVESTMENT_PACKAGE_PRICE * Globals::TOTAL_INVESTMENT_BONUS_PAYOUT * Globals::TOTAL_INVESTMENT_BONUS_PAYOUT) * $totalPackageInvested / $totalPackageSoldFromSystem;

                $eachInvestmentPackageFundReturn = $this->format2decimal($eachInvestmentPackageFundReturn);

                $eachPackageEarn = $eachInvestmentPackageFundReturn;
                print_r("totalPackageSoldFromSystem= " . $totalPackageSoldFromSystem . "<br><br>");
                print_r("totalPackageInvested= " . $totalPackageInvested . "<br><br>");
                print_r("eachPackageEarn= " . $eachPackageEarn . "<br><br>");

                $query = "SELECT SUM(package.total_package) AS _SUM, package.dist_id
                            FROM mlm_dist_investment_package package
                        LEFT JOIN mlm_distributor dist ON dist.distributor_id = package.dist_id
                        WHERE package.status_code = '" . Globals::STATUS_ACTIVE . "'
                            AND dist.status_code = '" . Globals::STATUS_ACTIVE . "'
                            AND package.bonus_release <> '" . Globals::STATUS_PENDING . "'
                        GROUP BY package.dist_id";
                //var_dump($query);

                $connection = Propel::getConnection();
                $statement = $connection->prepareStatement($query);
                $resultset = $statement->executeQuery();

                $totalPaidOutBonus = 0;
                while ($resultset->next()) {
                    $arr = $resultset->getRow();
                    if ($arr["_SUM"] != null) {
                        $distTotalInvestedPackage = $arr["_SUM"];
                        $distIdInvested = $arr["dist_id"];

                        print_r("Dist Id= " . $distIdInvested . "<br><br>");
                        if ($distIdInvested == Globals::SYSTEM_COMPANY_DIST_ID)
                            continue;
                        /******************************/
                        /*  package investment
                        /******************************/
                        // total networks
                        $c = new Criteria();
                        $c->add(MlmDistributorPeer::UPLINE_DIST_ID, $distIdInvested);
                        $c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
                        $totalNetworks = MlmDistributorPeer::doCount($c);

                        // getTotalTreePackageInvested
                        $totalTreePackageInvested = $this->getTotalTreePackageInvested($distIdInvested);
                        $totalFundManagementGain = $this->getTotalFundManagementGain($distIdInvested);

                        $bonusAmount = $this->format2decimal($eachPackageEarn * $distTotalInvestedPackage);
                        $totalPaidOutBonus += $bonusAmount;

                        if ($bonusAmount <= 0)
                            continue;

                        $distAccountEcashBalance = $this->getAccountBalance($distIdInvested, Globals::ACCOUNT_TYPE_ECASH);
                        $distAccountEcashBalance = $distAccountEcashBalance + $bonusAmount;

                        $mlm_account_ledger = new MlmAccountLedger();
                        $mlm_account_ledger->setDistId($distIdInvested);
                        $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
                        $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_FUND_MANAGEMENT);
                        $mlm_account_ledger->setRemark("");
                        $mlm_account_ledger->setCredit($bonusAmount);
                        $mlm_account_ledger->setDebit(0);
                        $mlm_account_ledger->setBalance($distAccountEcashBalance);
                        $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                        $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                        $mlm_account_ledger->save();

                        $accountId = $mlm_account_ledger->getAccountId();

                        $maintenanceCommission = $bonusAmount / 2;

                        $distAccountEcashBalance = $distAccountEcashBalance - $maintenanceCommission;

                        $mlm_account_ledger = new MlmAccountLedger();
                        $mlm_account_ledger->setDistId($distIdInvested);
                        $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
                        $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_MAINTENANCE);
                        $mlm_account_ledger->setRemark("");
                        $mlm_account_ledger->setCredit(0);
                        $mlm_account_ledger->setDebit($maintenanceCommission);
                        $mlm_account_ledger->setBalance($distAccountEcashBalance);
                        $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                        $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                        $mlm_account_ledger->save();

                        /******************************/
                        /*  Flushing
                        /******************************/
                        $flushLimit = 9999999999;
                        if ($totalNetworks == 0) {
                            $flushLimit = $distTotalInvestedPackage * 2 * Globals::INVESTMENT_PACKAGE_PRICE;
                        } else {
                            if ($totalTreePackageInvested >= ($distTotalInvestedPackage * 2 * Globals::INVESTMENT_PACKAGE_PRICE)) {
                                // infinity;
                            } else {
                                $flushLimit = $distTotalInvestedPackage * 4 * Globals::INVESTMENT_PACKAGE_PRICE;
                            }
                        }
                        $totalFundManagementGain = $totalFundManagementGain + $maintenanceCommission;

                        print_r("distIdInvested= " . $distIdInvested . "<br>");
                        print_r("totalFundManagementGain= " . $totalFundManagementGain . "<br>");
                        print_r("totalTreePackageInvested= " . $totalTreePackageInvested . "<br>");
                        print_r("distTotalInvestedPackage= " . $distTotalInvestedPackage . "<br>");
                        print_r("totalNetworks= " . $totalNetworks . "<br>");
                        if ($flushLimit < $totalFundManagementGain) {
                            $flushAmount = $totalFundManagementGain - $flushLimit;

                            if ($flushAmount > $maintenanceCommission) {
                                $flushAmount = $maintenanceCommission;
                            }
                            print_r("flushAmount= " . $flushAmount . "<br><br>");
                            $mlm_account_ledger = new MlmAccountLedger();
                            $mlm_account_ledger->setDistId($distIdInvested);
                            $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
                            $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_FLUSH);
                            $mlm_account_ledger->setRemark("Total Fund Management Gain:" . $totalFundManagementGain . ", Total Invested Package:" . $distTotalInvestedPackage . ", Total Networks:" . $totalNetworks . ", FLUSH LIMIT:" . $flushLimit);
                            $mlm_account_ledger->setCredit(0);
                            $mlm_account_ledger->setDebit($flushAmount);
                            $mlm_account_ledger->setBalance($distAccountEcashBalance - $flushAmount);
                            $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                            $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                            $mlm_account_ledger->save();

                            $distributorTerminatedDB = MlmDistributorPeer::retrieveByPK($distIdInvested);
                            $distributorTerminatedDB->setStatusCode(Globals::STATUS_TERMINATE);
                            $distributorTerminatedDB->save();
                        }

                        /******************************/
                        /*  Maintenance
                        /******************************/
                        $distAccountMaintenanceBalance = $this->getAccountBalance($distIdInvested, Globals::ACCOUNT_TYPE_MAINTENANCE);

                        $mlm_account_ledger = new MlmAccountLedger();
                        $mlm_account_ledger->setDistId($distIdInvested);
                        $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_MAINTENANCE);
                        $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_FUND_MANAGEMENT);
                        $mlm_account_ledger->setRemark("");
                        $mlm_account_ledger->setCredit($maintenanceCommission);
                        $mlm_account_ledger->setDebit(0);
                        $mlm_account_ledger->setBalance($distAccountMaintenanceBalance + $maintenanceCommission);
                        $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                        $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                        $mlm_account_ledger->save();

                        /******************************/
                        /*  Commission
                        /******************************/
                        $commissionBalance = $this->getCommissionBalance($distIdInvested, Globals::COMMISSION_TYPE_FUND_MANAGEMENT);

                        $sponsorDistCommissionledger = new MlmDistCommissionLedger();
                        $sponsorDistCommissionledger->setDistId($distIdInvested);
                        $sponsorDistCommissionledger->setCommissionType(Globals::COMMISSION_TYPE_FUND_MANAGEMENT);
                        $sponsorDistCommissionledger->setTransactionType(Globals::COMMISSION_LEDGER_INVESTMENT_PACKAGE);
                        $sponsorDistCommissionledger->setCredit($bonusAmount);
                        $sponsorDistCommissionledger->setDebit(0);
                        $sponsorDistCommissionledger->setAccountId($accountId);
                        $sponsorDistCommissionledger->setStatusCode(Globals::STATUS_ACTIVE);
                        $sponsorDistCommissionledger->setBalance($commissionBalance + $bonusAmount);
                        $sponsorDistCommissionledger->setRemark("");
                        $sponsorDistCommissionledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                        $sponsorDistCommissionledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                        $sponsorDistCommissionledger->save();

                        /******************************/
                        /*  Direct Sponsor Bonus
                        /******************************/
                        // 1000 * 0.9 (10 percent give to system maintenenace) * 0.9 (direct sponsor payout) * $totalInvestmentPackageSold / $totalPackageSoldFromSystem
                        //$eachInvestmentPackageFundReturn = (Globals::INVESTMENT_PACKAGE_PRICE * Globals::TOTAL_INVESTMENT_BONUS_PAYOUT * Globals::TOTAL_INVESTMENT_BONUS_PAYOUT) * $totalInvestmentPackageSold / $totalPackageSoldFromSystem;

                        $bonusPayout = $bonusAmount / (Globals::TOTAL_INVESTMENT_BONUS_PAYOUT * 10); // the 10% from the bonus
                        $firstLevel = $bonusPayout * 0.5;
                        $secondLevel = $bonusPayout * 0.3;
                        $thirdLevel = $bonusPayout * 0.2;

                        $level = 1;
                        $distributorDB = MlmDistributorPeer::retrieveByPK($distIdInvested);
                        $bonusDistributorCode = $distributorDB->getDistributorCode();
                        $uplineDistId = $distributorDB->getUplineDistId();

                        while ($level <= 3) {
                            if ($uplineDistId == null) {
                                break;
                            }
                            $remarks = "PURCHASE INVESTMENT PACKAGE (" . $bonusDistributorCode . "), tier:" . $level . ", bonus amount:" . $bonusAmount;
                            $remarksCN = $this->getContext()->getI18N()->__("PURCHASE INVESTMENT PACKAGE (%1%), tier:%2%, bonus amount:%3%", array('%1%' => $bonusDistributorCode, '%2%' => $level, '%3%' => $bonusAmount));

                            $distributorDB = MlmDistributorPeer::retrieveByPK($uplineDistId);
                            if (!$distributorDB) {
                                break;
                            }

                            if ($distributorDB->getStatusCode() != Globals::STATUS_ACTIVE || $this->entitiedInvestmentPackageBonus($uplineDistId) == false) {
                                $uplineDistId = $distributorDB->getUplineDistId();
                                //$level++;
                                continue;
                            }
                            //******************************
                            //*  upline commission ecash
                            //******************************

                            $distAccountEcashBalance = $this->getAccountBalance($uplineDistId, Globals::ACCOUNT_TYPE_ECASH);

                            $drbCommission = 0;
                            if ($level == 1) {
                                $drbCommission = $firstLevel;
                            } else if ($level == 2) {
                                $drbCommission = $secondLevel;
                            } else if ($level == 3) {
                                $drbCommission = $thirdLevel;
                            }
                            $distAccountEcashBalance = $distAccountEcashBalance + $drbCommission;

                            $mlm_account_ledger = new MlmAccountLedger();
                            $mlm_account_ledger->setDistId($uplineDistId);
                            $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
                            $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_DRB);
                            $mlm_account_ledger->setRemark($remarks);
                            $mlm_account_ledger->setCredit($drbCommission);
                            $mlm_account_ledger->setDebit(0);
                            $mlm_account_ledger->setBalance($distAccountEcashBalance);
                            $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                            $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                            $mlm_account_ledger->save();

                            $accountId = $mlm_account_ledger->getAccountId();

                            $commissionBalance = $this->getCommissionBalance($uplineDistId, Globals::COMMISSION_TYPE_GDB);

                            $sponsorDistCommissionledger = new MlmDistCommissionLedger();
                            $sponsorDistCommissionledger->setDistId($uplineDistId);
                            $sponsorDistCommissionledger->setCommissionType(Globals::COMMISSION_TYPE_DRB);
                            $sponsorDistCommissionledger->setTransactionType(Globals::COMMISSION_LEDGER_INVESTMENT);
                            $sponsorDistCommissionledger->setCredit($drbCommission);
                            $sponsorDistCommissionledger->setDebit(0);
                            $sponsorDistCommissionledger->setAccountId($accountId);
                            $sponsorDistCommissionledger->setStatusCode(Globals::STATUS_ACTIVE);
                            $sponsorDistCommissionledger->setBalance($commissionBalance + $drbCommission);
                            $sponsorDistCommissionledger->setRemark($remarks);
                            $sponsorDistCommissionledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                            $sponsorDistCommissionledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                            $sponsorDistCommissionledger->save();

                            if ($distributorDB) {
                                $uplineDistId = $distributorDB->getUplineDistId();
                            } else {
                                break;
                            }
                            $level++;
                        }
                    }
                }

                $this->unFreezeInvestmentPackage();

                $mlm_dist_investment_package_batch->setTotalPackage($totalPackageInvested);
                $mlm_dist_investment_package_batch->setPaidOutBonus($totalPaidOutBonus);
                $mlm_dist_investment_package_batch->setBonusPerPackage($eachInvestmentPackageFundReturn);
                $mlm_dist_investment_package_batch->setStatusCode(Globals::STATUS_COMPLETE);
                $mlm_dist_investment_package_batch->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_dist_investment_package_batch->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_dist_investment_package_batch->save();
            }

            // auto purchase investment package
            $sql = "SELECT dist_id, SUM(credit - debit) as maintenance_credit
                        FROM mlm_account_ledger WHERE account_type = 'MAINTENANCE'
                    GROUP BY dist_id  HAVING SUM(credit - debit) >= " . Globals::INVESTMENT_PACKAGE_PRICE;

            $connection = Propel::getConnection();
            $statement = $connection->prepareStatement($sql);
            $resultset = $statement->executeQuery();

            print_r("maintenance_credit<br>");

            while ($resultset->next()) {
                $arr = $resultset->getRow();
                $maintenance_credit = $arr["maintenance_credit"];
                $distIdInvested = $arr["dist_id"];

                //$modulusPrice = $maintenance_credit % Globals::INVESTMENT_PACKAGE_PRICE;
                $investmentPackageUnit = $maintenance_credit / Globals::INVESTMENT_PACKAGE_PRICE;

                $totalAmount = floor($investmentPackageUnit) * Globals::INVESTMENT_PACKAGE_PRICE;
                $totalAmount = $totalAmount;
                $maintenance_credit = $maintenance_credit - $totalAmount;

                $mlm_account_ledger = new MlmAccountLedger();
                $mlm_account_ledger->setDistId($distIdInvested);
                $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_MAINTENANCE);
                $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_INVESTMENT);
                $mlm_account_ledger->setRemark("PURCHASE INVESTMENT PACKAGE");
                $mlm_account_ledger->setCredit(0);
                $mlm_account_ledger->setDebit($totalAmount);
                $mlm_account_ledger->setBalance($maintenance_credit);
                $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_account_ledger->save();

                /******************************/
                /*  mlm_dist_investment_package
                /******************************/
                $c = new Criteria();
                $c->add(MlmDistInvestmentPackageBatchPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
                $mlm_dist_investment_package_batch = MlmDistInvestmentPackageBatchPeer::doSelectOne($c);

                if (!$mlm_dist_investment_package_batch) {
                    $dateUtil = new DateUtil();
                    $date = $dateUtil->formatDate("Y-m-d", $dateUtil->addDate(date("Y-m-d"), Globals::BONUS_CALCULATION_DAY, 0, 0));

                    $mlm_dist_investment_package_batch = new MlmDistInvestmentPackageBatch();
                    $mlm_dist_investment_package_batch->setTotalPackage($investmentPackageUnit);
                    $mlm_dist_investment_package_batch->setBonusDate($date);
                    $mlm_dist_investment_package_batch->setStatusCode(Globals::STATUS_ACTIVE);
                    $mlm_dist_investment_package_batch->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $mlm_dist_investment_package_batch->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $mlm_dist_investment_package_batch->save();
                } else {
                    $totalPackage = $investmentPackageUnit + $mlm_dist_investment_package_batch->getTotalPackage();
                    $mlm_dist_investment_package_batch->setTotalPackage($totalPackage);
                    $mlm_dist_investment_package_batch->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $mlm_dist_investment_package_batch->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $mlm_dist_investment_package_batch->save();
                }

                $mlm_dist_investment_package = new MlmDistInvestmentPackage();
                $mlm_dist_investment_package->setBatchId($mlm_dist_investment_package_batch->getBatchId());
                $mlm_dist_investment_package->setDistId(Globals::SYSTEM_COMPANY_DIST_ID);
                $mlm_dist_investment_package->setTotalPackage($investmentPackageUnit);
                $mlm_dist_investment_package->setTotalAmount($totalAmount);
                $mlm_dist_investment_package->setStatusCode(Globals::STATUS_ACTIVE);
                $mlm_dist_investment_package->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_dist_investment_package->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_dist_investment_package->save();
            }

            $c = new Criteria();
            $c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
            $dists = MlmDistributorPeer::doSelect($c);
            foreach ($dists as $dist) {
                $c = new Criteria();
                $c->add(MlmDistInvestmentPeer::DIST_ID, $dist->getDistributorId());
                $c->add(MlmDistInvestmentPeer::EXPIRED_DATE, date("Y-m-d H:i:s"), Criteria::GREATER_THAN);
                $mlmDistInvestment = MlmDistInvestmentPeer::doSelectOne($c);


                //print_r("<br>");
                if (!$mlmDistInvestment) {
                    //print_r("not exist");
                    $dist->setStatusCode(Globals::STATUS_INACTIVE);
                    $dist->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $dist->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $dist->save();
                } else {
                    //print_r("exist");
                }
            }

            //$bonusDate = $dateUtil->formatDate("Y-m-d", $dateUtil->addDate($bonusDate, 1, 0, 0));
            $mlm_daily_bonus_log = new MlmDailyBonusLog();
            $mlm_daily_bonus_log->setAccessIp($this->getRequest()->getHttpHeader('addr', 'remote'));
            $mlm_daily_bonus_log->setBonusType(Globals::DAILY_BONUS_LOG_TYPE_DAILY);
            $mlm_daily_bonus_log->setBonusDate(date("Y-m-d H:i:s"));
            $mlm_daily_bonus_log->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_daily_bonus_log->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_daily_bonus_log->save();
            //$level++;
            //}
            //}
            $con->commit();
        } catch (PropelException $e) {
            $con->rollback();
            //throw $e;
        }

        print_r("<br>sendDailyReport<br>");
        $this->sendDailyReport();
        print_r("Done");
        return sfView::HEADER_ONLY;
    }

    function sendDailyReport()
    {
        $body = "";
        $body .= $this->getAllBonusData();
        $body .= $this->getPackageSaleData();

        $sendMailService = new SendMailService();
        $dateUtil = new DateUtil();
        $subject = "Daily Report " . $dateUtil->formatDate("Y-m-d", $dateUtil->addDate(date("Y-m-d"), -1, 0, 0));

        //        $sendMailService->sendMail("r9jason@gmail.com", "Boss", $subject, $body, Mails::EMAIL_SENDER, "r9jason@gmail.com");
        $sendMailService->sendMail("jayc_jcpc@hotmail.com", "Boss", $subject, $body, Mails::EMAIL_SENDER, "r9jason@gmail.com");
    }

    function getAllBonusData()
    {

        $bonusService = new BonusService();

        $body = "<h3>All Bonus Data</h3><table width='100%' style='border-color: #DDDDDD -moz-use-text-color -moz-use-text-color #DDDDDD;border-image: none; border-style: solid none none solid;border-width: 1px 0 0 1px;'>
                    <thead>
                    <tr>
                        <th style='background-color: #CCCCFF; padding: 2px; text-align: left;'>DATE</th>
                        <th style='background-color: #CCCCFF; padding: 2px; text-align: left;'>DRB</th>
                        <th style='background-color: #CCCCFF; padding: 2px; text-align: left;'>GENERATION BONUS</th>
                        <th style='background-color: #CCCCFF; padding: 2px; text-align: left;'>OVERRIDING BONUS</th>
                        <th style='background-color: #CCCCFF; padding: 2px; text-align: left;'>PAIRING BONUS</th>
                        <th style='background-color: #CCCCFF; padding: 2px; text-align: left;'>UP 1 DOWN 3</th>
                    </tr>
                    </thead>
                    <tbody>";

        $dateUtil = new DateUtil();

        for ($i = 0; $i < 3; $i++) {
            $queryDate = $dateUtil->formatDate("Y-m-d", $dateUtil->addDate(date("Y-m-d"), ($i + 1) * -1, 0, 0));
            $queryDateForGrb = $dateUtil->formatDate("Y-m-d", $dateUtil->addDate(date("Y-m-d"), $i * -1, 0, 0));

            $totalDrb = $bonusService->doCalculateDrb($queryDate);
            $totalGenerationBonus = $bonusService->doCalculateGenerationBonus($queryDate);
            $overridingBonus = $bonusService->doCalculateOverridingBonus($queryDate);
            $pairingBonus = 0;
            $up1Down3 = 0;

            $body .= "<tr class='sf_admin_row_1'>
                    <td style='background-color: #EEEEFF; border-bottom: 1px solid #DDDDDD; border-right: 1px solid #DDDDDD; padding: 3px;'>" . $queryDate . "</td>
                    <td style='background-color: #EEEEFF; border-bottom: 1px solid #DDDDDD; border-right: 1px solid #DDDDDD; padding: 3px;'>" . number_format($totalDrb, 2) . "</td>
                    <td style='background-color: #EEEEFF; border-bottom: 1px solid #DDDDDD; border-right: 1px solid #DDDDDD; padding: 3px;'>" . number_format($totalGenerationBonus, 2) . "</td>
                    <td style='background-color: #EEEEFF; border-bottom: 1px solid #DDDDDD; border-right: 1px solid #DDDDDD; padding: 3px;'>" . number_format($overridingBonus, 2) . "</td>
                    <td style='background-color: #EEEEFF; border-bottom: 1px solid #DDDDDD; border-right: 1px solid #DDDDDD; padding: 3px;'>" . number_format($pairingBonus, 2) . "</td>
                    <td style='background-color: #EEEEFF; border-bottom: 1px solid #DDDDDD; border-right: 1px solid #DDDDDD; padding: 3px;'>" . number_format($up1Down3, 2) . "</td>
                </tr>";
        }

        $body .= "</tbody>
                </table>";

        return $body;
    }

    function getPackageSaleData()
    {
        $bonusService = new BonusService();
        $dateUtil = new DateUtil();
        $queryDate = $dateUtil->formatDate("Y-m-d", $dateUtil->addDate(date("Y-m-d"), -1, 0, 0));
        $packageArrs = $bonusService->doCalculatePackage($queryDate);

        $body = "<h3>Sales for today</h3><table width='100%' style='border-color: #DDDDDD -moz-use-text-color -moz-use-text-color #DDDDDD;border-image: none; border-style: solid none none solid;border-width: 1px 0 0 1px;'>
                    <thead>
                    <tr>
                        <th style='background-color: #CCCCFF; padding: 2px; text-align: left;'>Package Name</th>
                        <th style='background-color: #CCCCFF; padding: 2px; text-align: left;'>Qty</th>
                        <th style='background-color: #CCCCFF; padding: 2px; text-align: left;'>Price</th>
                        <th style='background-color: #CCCCFF; padding: 2px; text-align: left;'>Sub Total</th>
                    </tr>
                    </thead>
                    <tbody>";

        $totalAmount = 0;
        foreach ($packageArrs as $packageArr) {
            $totalAmount = $totalAmount + ($packageArr["qty"] * $packageArr["price"]);
            $body .= "<tr class='sf_admin_row_1'>
                        <td style='background-color: #EEEEFF; border-bottom: 1px solid #DDDDDD; border-right: 1px solid #DDDDDD; padding: 3px;'>" . $packageArr['name'] . "</td>
                        <td style='background-color: #EEEEFF; border-bottom: 1px solid #DDDDDD; border-right: 1px solid #DDDDDD; padding: 3px;'>" . $packageArr['qty'] . "</td>
                        <td style='background-color: #EEEEFF; border-bottom: 1px solid #DDDDDD; border-right: 1px solid #DDDDDD; padding: 3px;'>" . number_format($packageArr['price'], 2) . "</td>
                        <td style='background-color: #EEEEFF; border-bottom: 1px solid #DDDDDD; border-right: 1px solid #DDDDDD; padding: 3px;'>" . number_format($packageArr["qty"] * $packageArr["price"], 2) . "</td>
                    </tr>";
        }
        $body .= "<tr class='sf_admin_row_1'>
            <td colspan='3' align='right' style='background-color: #EEEEFF; border-bottom: 1px solid #DDDDDD; border-right: 1px solid #DDDDDD; padding: 3px;'>Total Amount</td>
            <td style='background-color: #EEEEFF; border-bottom: 1px solid #DDDDDD; border-right: 1px solid #DDDDDD; padding: 3px;'>" . $totalAmount . "</td>
        </tr>";
        $body .= "</tbody>
                </table>";

        return $body;
    }

    public function executeDownloadMt4()
    {
        $response = $this->getResponse();
        $response->clearHttpHeaders();
        $response->addCacheControlHttpHeader('Cache-control', 'must-revalidate, post-check=0, pre-check=0');
        $response->setContentType('application/exe');
        $response->setHttpHeader('Content-Transfer-Encoding', 'binary', TRUE);
        $response->setHttpHeader('Content-Disposition', 'attachment; filename=pro4setup.exe', TRUE);
        $response->sendHttpHeaders();
        readfile(sfConfig::get('sf_upload_dir') . "/pro4setup.exe");
        return sfView::NONE;
    }

    public function executeConvertCp2ToCp1()
    {
        $this->getUser()->setAttribute(Globals::SESSION_MAIN_MENU, "MY_ACCOUNT");
        $this->getUser()->setAttribute(Globals::SESSION_SUB_MENU, "CONVERT_CP2_TO_CP1");

        $ledgerAccountBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);
        $this->ledgerAccountBalance = $ledgerAccountBalance;

        $epointAmount = $this->getRequestParameter('epointAmount');

        if ($this->getRequestParameter('epointAmount') > 0 && $this->getRequestParameter('transactionPassword') <> "") {
            $tbl_user = AppUserPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_USERID));

            if ($epointAmount > $ledgerAccountBalance) {
                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("In-sufficient CP2"));

            } elseif (strtoupper($tbl_user->getUserpassword2()) <> strtoupper($this->getRequestParameter('transactionPassword'))) {
                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Invalid Security password"));

            } elseif ($epointAmount > 0) {
                $ledgerEPointBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_EPOINT);

                $tbl_account_ledger = new MlmAccountLedger();
                $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
                $tbl_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_CONVERT_EPOINT);
                $tbl_account_ledger->setCredit(0);
                $tbl_account_ledger->setDebit($epointAmount);
                $tbl_account_ledger->setBalance($ledgerAccountBalance - $epointAmount);
                $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->save();

                $tbl_account_ledger = new MlmAccountLedger();
                $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_EPOINT);
                $tbl_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_CONVERT);
                $tbl_account_ledger->setCredit($epointAmount);
                $tbl_account_ledger->setDebit(0);
                $tbl_account_ledger->setBalance($ledgerEPointBalance + $epointAmount);
                $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->save();

                //$this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);
                //$this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_EPOINT);

                $this->setFlash('successMsg', $this->getContext()->getI18N()->__("CP2 convert to CP1 successful."));

                return $this->redirect('/member/convertCp2ToCp1');
            }
        }
    }

    public function executeConvertMt4ToEcash()
    {
        $ledgerAccountBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_MT4);
        $this->ledgerAccountBalance = $ledgerAccountBalance;

        $epointAmount = $this->getRequestParameter('epointAmount');

        if ($this->getRequestParameter('epointAmount') > 0 && $this->getRequestParameter('transactionPassword') <> "") {
            $tbl_user = AppUserPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_USERID));

            if ($epointAmount > $ledgerAccountBalance) {
                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("In-sufficient MT4 wallet amount"));

            } elseif (strtoupper($tbl_user->getUserpassword2()) <> strtoupper($this->getRequestParameter('transactionPassword'))) {
                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Invalid Security password"));

            } elseif ($epointAmount > 0) {
                $ledgerEPointBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);

                $tbl_account_ledger = new MlmAccountLedger();
                $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_MT4);
                $tbl_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_CONVERT_ECASH);
                $tbl_account_ledger->setCredit(0);
                $tbl_account_ledger->setDebit($epointAmount);
                $tbl_account_ledger->setBalance($ledgerAccountBalance - $epointAmount);
                $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->save();

                $tbl_account_ledger = new MlmAccountLedger();
                $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
                $tbl_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_CONVERT_FROM_MT4);
                $tbl_account_ledger->setCredit($epointAmount);
                $tbl_account_ledger->setDebit(0);
                $tbl_account_ledger->setBalance($ledgerEPointBalance + $epointAmount);
                $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->save();

                //$this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);
                //$this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_EPOINT);

                $this->setFlash('successMsg', $this->getContext()->getI18N()->__("MT4 wallet convert to CP2 successful."));

                return $this->redirect('/member/convertMt4ToEcash');
            }
        }
    }

    public function executePurchasePackage()
    {
        $pendingDistId = $this->getRequestParameter('p');

        $c = new Criteria();

        $c->add(MlmDistributorPeer::DISTRIBUTOR_ID, $pendingDistId);
        $c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_PENDING);
        $pendingDistDB = MlmDistributorPeer::doSelectOne($c);
        $this->forward404Unless($pendingDistDB);

        $c = new Criteria();
        $c->add(MlmPackagePeer::PUBLIC_PURCHASE, 1);
        $packageDBs = MlmPackagePeer::doSelect($c);

        $this->systemCurrency = $this->getAppSetting(Globals::SETTING_SYSTEM_CURRENCY);
        $this->pointAvailable = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_EPOINT);
        $this->pendingDistDB = $pendingDistDB;
        $this->packageDBs = $packageDBs;
    }

    public function executePackageUpgrade()
    {
        if ($this->getRequestParameter('transactionPassword') <> "" && $this->getRequestParameter('pid') <> "") {
            $ledgerECashBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);
            $ledgerEPointBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_EPOINT);

            $ecashPaid = $this->getRequestParameter('ecashPaid');
            $epointPaid = $this->getRequestParameter('epointPaid');
            $walletType = $this->getRequestParameter('walletType', 'ECASH');

            $distDB = MlmDistributorPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $distId = $distDB->getDistributorId();

            $distPackage = MlmPackagePeer::retrieveByPK($distDB->getRankId());
            $currentPackageAmount = $distPackage->getPrice();

            $selectedPackage = MlmPackagePeer::retrieveByPK($this->getRequestParameter('pid'));

            $amountNeeded = $selectedPackage->getPrice() - $currentPackageAmount;

            if ($epointPaid > $ledgerEPointBalance) {
                $this->setFlash('errorMsg', "In-sufficient CP1 amount");
                return $this->redirect('/member/packageUpgrade');
            }

            if ($ecashPaid > $ledgerECashBalance) {
                $this->setFlash('errorMsg', "In-sufficient CP2 amount");
                return $this->redirect('/member/packageUpgrade');
            }
            $totalPaid = $epointPaid + $ecashPaid;
            if ($totalPaid < $amountNeeded) {
                $this->setFlash('errorMsg', "In-sufficient fund to purchase package");
                return $this->redirect('/member/packageUpgrade');
            }
            if ($totalPaid > $amountNeeded) {
                $this->setFlash('errorMsg', "Amount Paid is not tally with package price");
                return $this->redirect('/member/packageUpgrade');
            }

            $tbl_user = AppUserPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_USERID));
            if (strtoupper($tbl_user->getUserpassword2()) <> strtoupper($this->getRequestParameter('transactionPassword'))) {
                $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Invalid Security password"));
                return $this->redirect('/member/packageUpgrade');
            } else {
                $con = Propel::getConnection(MlmDistributorPeer::DATABASE_NAME);
                try {
                    $con->begin();

                    if ($epointPaid > 0) {
                        $ledgerEPointBalance = $ledgerEPointBalance - $epointPaid;

                        $mlm_account_ledger = new MlmAccountLedger();
                        $mlm_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                        $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_EPOINT);
                        $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_REGISTER);
                        $mlm_account_ledger->setRemark("PACKAGE UPGRADED FROM " . $distPackage->getPackageName() . " => " . $selectedPackage->getPackageName());
                        $mlm_account_ledger->setCredit(0);
                        $mlm_account_ledger->setDebit($epointPaid);
                        $mlm_account_ledger->setBalance($ledgerEPointBalance);
                        $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                        $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                        $mlm_account_ledger->save();
                    }
                    if ($ecashPaid > 0) {
                        $ledgerECashBalance = $ledgerECashBalance - $ecashPaid;

                        $accountType = Globals::ACCOUNT_TYPE_ECASH;

                        $mlm_account_ledger = new MlmAccountLedger();
                        $mlm_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                        $mlm_account_ledger->setAccountType($accountType);
                        $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_REGISTER);
                        $mlm_account_ledger->setRemark("PACKAGE UPGRADED FROM " . $distPackage->getPackageName() . " => " . $selectedPackage->getPackageName());
                        $mlm_account_ledger->setCredit(0);
                        $mlm_account_ledger->setDebit($ecashPaid);
                        $mlm_account_ledger->setBalance($ledgerECashBalance);
                        $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                        $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                        $mlm_account_ledger->save();
                    }

                    // ******       company account      ****************
                    $mlmPackageUpgradeHistory = new MlmPackageUpgradeHistory();
                    $mlmPackageUpgradeHistory->setDistId($distId);
                    $mlmPackageUpgradeHistory->setTransactionCode(Globals::ACCOUNT_LEDGER_ACTION_PACKAGE_UPGRADE);
                    $mlmPackageUpgradeHistory->setAmount($amountNeeded);
                    $mlmPackageUpgradeHistory->setPackageId($selectedPackage->getPackageId());
                    $mlmPackageUpgradeHistory->setStatusCode(Globals::STATUS_ACTIVE);
                    $mlmPackageUpgradeHistory->setRemarks("PACKAGE UPGRADED FROM " . $distPackage->getPackageName() . " => " . $selectedPackage->getPackageName());
                    $mlmPackageUpgradeHistory->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $mlmPackageUpgradeHistory->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $mlmPackageUpgradeHistory->save();

                    $distDB->setRankId($selectedPackage->getPackageId());
                    $distDB->setRankCode($selectedPackage->getPackageName());
                    $distDB->save();

                    /******************************/
                    /*  Direct Sponsor Bonus
                    /******************************/
                    $packagePrice = $amountNeeded;
                    $mlm_distributor = $distDB;
                    $sponsorId = $mlm_distributor->getDistributorId();
                    //var_dump($mlm_distributor->getUplineDistId());
                    //exit();
                    if ($mlm_distributor->getUplineDistId() != null) {
                        $uplineDistId = $mlm_distributor->getUplineDistId();
                        $uplineDistDB = MlmDistributorPeer::retrieveByPK($mlm_distributor->getUplineDistId());
                        /**************************************/
                        /*  Direct REFERRAL Bonus For Upline
                        /**************************************/
                        if ($uplineDistDB) {
                            if ($uplineDistDB->getIsIb() == Globals::YES) {
                                $directSponsorPercentage = $uplineDistDB->getIbCommission() * 100;
                                $directSponsorBonusAmount = $directSponsorPercentage * $packagePrice / 100;
                            } else {
                                $uplineDistPackage = MlmPackagePeer::retrieveByPK($uplineDistDB->getRankId());
                                $directSponsorPercentage = $uplineDistPackage->getCommission();
                                $directSponsorBonusAmount = $directSponsorPercentage * $packagePrice / 100;
                            }
                            $totalBonusPayOut = $directSponsorPercentage;

                            $firstForDRB = true;
                            while ($totalBonusPayOut <= Globals::TOTAL_BONUS_PAYOUT) {
                                if ($uplineDistId == null || $uplineDistId == 0)
                                    break;

                                $distAccountEcashBalance = $this->getAccountBalance($uplineDistId, Globals::ACCOUNT_TYPE_ECASH);

                                if ($firstForDRB == true) {
                                    $commissionType = Globals::ACCOUNT_LEDGER_ACTION_DRB;
                                } else {
                                    $commissionType = Globals::ACCOUNT_LEDGER_ACTION_OVERRIDING_BONUS;
                                }

                                $mlm_account_ledger = new MlmAccountLedger();
                                $mlm_account_ledger->setDistId($uplineDistId);
                                $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
                                $mlm_account_ledger->setTransactionType($commissionType);
                                $mlm_account_ledger->setRemark("DRB FOR PACKAGE UPGRADED (" . $selectedPackage->getPackageName() . "), PACKAGE PRICE: " . $packagePrice . ", COMMISSION: " . $directSponsorPercentage . "%, MEMBER ID: " . $mlm_distributor->getDistributorCode());
                                $mlm_account_ledger->setCredit($directSponsorBonusAmount);
                                $mlm_account_ledger->setDebit(0);
                                $mlm_account_ledger->setBalance($distAccountEcashBalance + $directSponsorBonusAmount);
                                $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                                $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                                $mlm_account_ledger->save();

                                //$this->revalidateAccount($uplineDistId, Globals::ACCOUNT_TYPE_ECASH);

                                /******************************/
                                /*  Commission
                                /******************************/
                                $dsbBalance = $this->getCommissionBalance($uplineDistId, Globals::COMMISSION_TYPE_DRB);

                                $sponsorDistCommissionledger = new MlmDistCommissionLedger();
                                $sponsorDistCommissionledger->setDistId($uplineDistId);
                                $sponsorDistCommissionledger->setCommissionType($commissionType);
                                $sponsorDistCommissionledger->setTransactionType(Globals::COMMISSION_LEDGER_REGISTER);
                                $sponsorDistCommissionledger->setCredit($directSponsorBonusAmount);
                                $sponsorDistCommissionledger->setDebit(0);
                                $sponsorDistCommissionledger->setStatusCode(Globals::STATUS_ACTIVE);
                                $sponsorDistCommissionledger->setBalance($dsbBalance + $directSponsorBonusAmount);
                                if ($firstForDRB == true) {
                                    $sponsorDistCommissionledger->setRemark("DRB FOR PACKAGE UPGRADED (" . $selectedPackage->getPackageName() . "), PACKAGE PRICE: " . $packagePrice . ", COMMISSION: " . $directSponsorPercentage . "%, MEMBER ID: " . $mlm_distributor->getDistributorCode());
                                    $firstForDRB = false;
                                } else {
                                    $sponsorDistCommissionledger->setRemark("OVERRIDING BONUS FOR PACKAGE UPGRADED (" . $selectedPackage->getPackageName() . "), PACKAGE PRICE: " . $packagePrice . ", COMMISSION: " . $directSponsorPercentage . "%, MEMBER ID: " . $mlm_distributor->getDistributorCode());
                                }
                                $sponsorDistCommissionledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                                $sponsorDistCommissionledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                                $sponsorDistCommissionledger->save();

                                //                    $this->revalidateCommission($uplineDistId, Globals::COMMISSION_TYPE_DRB);
                                //var_dump("==>1");
                                //var_dump("totalBonusPayOut=".$totalBonusPayOut);
                                if ($totalBonusPayOut < Globals::TOTAL_BONUS_PAYOUT) {
                                    //var_dump("==>2");
                                    $checkCommission = true;
                                    $uplineDistId = $uplineDistDB->getUplineDistId();
                                    while ($checkCommission == true) {
                                        //var_dump("==>3**".$uplineDistId);
                                        $uplineDistDB = MlmDistributorPeer::retrieveByPK($uplineDistId);

                                        //var_dump("==>3$$".$uplineDistId);
                                        if (!$uplineDistDB) {
                                            break;
                                        }

                                        if ($uplineDistDB->getIsIb() == Globals::YES) {
                                            /*if ($uplineDistDB->getIbRankId() != null) {
                                                $uplineDistPackage = MlmIbPackagePeer::retrieveByPK($uplineDistDB->getIbRankId());
                                            } else {
                                                $uplineDistPackage = MlmPackagePeer::retrieveByPK($uplineDistDB->getRankId());
                                            }*/
                                            $directSponsorPercentage = $uplineDistDB->getIbCommission() * 100;
                                        } else {
                                            $uplineDistPackage = MlmPackagePeer::retrieveByPK($uplineDistDB->getRankId());
                                            $directSponsorPercentage = $uplineDistPackage->getCommission();
                                        }
                                        if ($directSponsorPercentage > $totalBonusPayOut) {
                                            //var_dump("==>6");
                                            $directSponsorPercentage = $directSponsorPercentage - $totalBonusPayOut;
                                            $totalBonusPayOut += $directSponsorPercentage;
                                            if ($totalBonusPayOut > Globals::TOTAL_BONUS_PAYOUT) {
                                                //var_dump("==>7");
                                                $directSponsorPercentage = $directSponsorPercentage - ($totalBonusPayOut - Globals::TOTAL_BONUS_PAYOUT);
                                            }
                                        } else {
                                            //var_dump("==>8");
                                            $uplineDistId = $uplineDistDB->getUplineDistId();
                                            continue;
                                        }

                                        $directSponsorBonusAmount = $directSponsorPercentage * $selectedPackage->getPrice() / 100;
                                        $checkCommission == false;
                                        break;
                                        //var_dump("==>9");
                                    }
                                } else {
                                    break;
                                    //var_dump("==>^^");
                                }
                            }
                        }
                    }


                    /* ****************************************************
                   * Generation Bonus
                   * ***************************************************/
                    $treeSponsorUplineDistId = $mlm_distributor->getUplineDistId();
                    $previousLevelCommission = 0;

                    //print_r("treeSponsorUplineDistId=".$treeSponsorUplineDistId);
                    //print_r("<br>");

                    for ($generationLevel = 1; $generationLevel <= 6; $generationLevel++) {
                        //print_r("<br>");
                        //print_r("<br>");
                        //print_r("generationLevel=".$generationLevel);
                        //print_r("<br>");

                        $treeUplineDistDB = MlmDistributorPeer::retrieveByPK($treeSponsorUplineDistId);
                        if (!$treeUplineDistDB) {
                            //print_r("first break=");
                            //print_r("<br>");
                            break;
                        }

                        $treeUplinePackageDB = MlmPackagePeer::retrieveByPK($treeUplineDistDB->getRankId());
                        if (!$treeUplinePackageDB) {
                            //print_r("sec break=");
                            //print_r("<br>");
                            break;
                        }

                        if ($generationLevel == 1) {
                            //print_r("1111111111=");
                            //print_r("<br>");
                            $treeSponsorUplineDistId = $treeUplineDistDB->getUplineDistId();
                            $previousLevelCommission = $treeUplinePackageDB->getCommission();
                            continue;
                        }

                        if ($treeUplinePackageDB->getCommission() <= $previousLevelCommission) {
                            //print_r("getCommission=".$treeUplinePackageDB->getCommission());
                            //print_r("<br>");
                            //print_r("previousLevelCommission=".$previousLevelCommission);
                            //print_r("<br>");
                            //print_r("treeUplineDistDB->getRankId()=".$treeUplineDistDB->getRankId());
                            //print_r("<br>");
                            /*var_dump($treeSponsorUplineDistId);
                            var_dump("<br>");
                            var_dump($treeUplineDistDB->getRankId());
                            var_dump("<br>");
                            var_dump($generationLevel);*/
                            $c = new Criteria();
                            $c->add(MlmPackageGdbPeer::PACKAGE_ID, $treeUplineDistDB->getRankId());
                            $c->add(MlmPackageGdbPeer::GENERATION, $generationLevel - 1);
                            $treeUplinePackageGdbDB = MlmPackageGdbPeer::doSelectOne($c);

                            if (!$treeUplinePackageGdbDB) {
                                $treeSponsorUplineDistId = $treeUplineDistDB->getUplineDistId();
                                //print_r("3rd break=");
                                //print_r("<br>");
                                continue;
                            } else {
                                //print_r("YESSSSSSSS=");
                                //print_r("<br>");
                                $gdbBonus = $packagePrice * $treeUplinePackageGdbDB->getCommission() / 100;

                                $distAccountEcashBalance = $this->getAccountBalance($treeSponsorUplineDistId, Globals::ACCOUNT_TYPE_ECASH);

                                $mlm_account_ledger = new MlmAccountLedger();
                                $mlm_account_ledger->setDistId($treeSponsorUplineDistId);
                                $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
                                $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_GENERATION_BONUS);
                                $mlm_account_ledger->setRemark("PACKAGE UPGRADED: " . $selectedPackage->getPackageName() . ", PACKAGE PRICE: " . $packagePrice . ", MEMBER ID: " . $mlm_distributor->getDistributorCode());
                                $mlm_account_ledger->setCredit($gdbBonus);
                                $mlm_account_ledger->setDebit(0);
                                $mlm_account_ledger->setBalance($distAccountEcashBalance + $gdbBonus);
                                $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                                $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                                $mlm_account_ledger->save();

                                /******************************/
                                /*  Commission
                                /******************************/
                                $dsbBalance = $this->getCommissionBalance($treeSponsorUplineDistId, Globals::COMMISSION_TYPE_GENERATION_BONUS);

                                $sponsorDistCommissionledger = new MlmDistCommissionLedger();
                                $sponsorDistCommissionledger->setDistId($treeSponsorUplineDistId);
                                $sponsorDistCommissionledger->setCommissionType(Globals::COMMISSION_TYPE_GENERATION_BONUS);
                                $sponsorDistCommissionledger->setTransactionType(Globals::COMMISSION_LEDGER_REGISTER);
                                $sponsorDistCommissionledger->setCredit($gdbBonus);
                                $sponsorDistCommissionledger->setDebit(0);
                                $sponsorDistCommissionledger->setStatusCode(Globals::STATUS_ACTIVE);
                                $sponsorDistCommissionledger->setBalance($dsbBalance + $gdbBonus);
                                $sponsorDistCommissionledger->setRemark("PACKAGE UPGRADED: " . $selectedPackage->getPackageName() . ", PACKAGE PRICE: " . $packagePrice . ", MEMBER ID: " . $mlm_distributor->getDistributorCode());
                                $sponsorDistCommissionledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                                $sponsorDistCommissionledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                                $sponsorDistCommissionledger->save();
                            }
                        }

                        $treeSponsorUplineDistId = $treeUplineDistDB->getUplineDistId();

                        //print_r("FINALLLLLLLLLLLL=".$treeSponsorUplineDistId);
                        //print_r("<br>");
                        if ($treeSponsorUplineDistId == null)
                            break;

                        if ($treeUplinePackageDB->getCommission() > $previousLevelCommission)
                            $previousLevelCommission = $treeUplinePackageDB->getCommission();
                    }

                    // **********************************************************************************************
                    // ************************         check auto upgrade package          *************************
                    // **********************************************************************************************
                    $sponsorUplineDistId = $mlm_distributor->getUplineDistId();
                    $uplineDistDB = MlmDistributorPeer::retrieveByPK($sponsorUplineDistId);
                    $uplinePackageDB = MlmPackagePeer::retrieveByPK($uplineDistDB->getRankId());

                    $promotePackageIdNeeded = $uplinePackageDB->getPromotePackageIdNeeded();
                    $promotePackageQtyNeeded = $uplinePackageDB->getPromotePackageQtyNeeded();
                    $promotePackageUpgradeTo = $uplinePackageDB->getPromotePackageUpgradeTo();
                    $totalSponsorAmount = $uplinePackageDB->getTotalSponsorAmount();
                    $totalSponsorAmountUpgradeTo = $uplinePackageDB->getTotalSponsorAmountUpgradeTo();

                    $isUpgraded = false;
                    if ($totalSponsorAmount != null && $totalSponsorAmountUpgradeTo != null) {
                        //var_dump("1111111");
                        //var_dump("<br>");
                        $maxPackagePrice = $this->getMaxSponsorPackagePrice($sponsorUplineDistId);
                        //var_dump("maxPackagePrice:".$maxPackagePrice);
                        //var_dump("<br>");
                        //var_dump("totalSponsorAmount:".$totalSponsorAmount);
                        //var_dump("<br>");
                        if ($maxPackagePrice >= $totalSponsorAmount) {
                            $upgradedPackage = MlmPackagePeer::retrieveByPK($totalSponsorAmountUpgradeTo);

                            $mlmPackageUpgradeHistory = new MlmPackageUpgradeHistory();
                            $mlmPackageUpgradeHistory->setDistId($sponsorUplineDistId);
                            $mlmPackageUpgradeHistory->setTransactionCode(Globals::ACCOUNT_LEDGER_ACTION_PACKAGE_PROMO);
                            $mlmPackageUpgradeHistory->setAmount(0);
                            $mlmPackageUpgradeHistory->setPackageId($upgradedPackage->getPackageId());
                            $mlmPackageUpgradeHistory->setStatusCode(Globals::STATUS_ACTIVE);
                            $mlmPackageUpgradeHistory->setRemarks("PACKAGE UPGRADED FROM " . $uplinePackageDB->getPackageName() . " => " . $upgradedPackage->getPackageName() . ", PACKAGE AMOUNT NEEDED:" . $totalSponsorAmount . ", SPONSOR AMOUNT:" . $maxPackagePrice);
                            $mlmPackageUpgradeHistory->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                            $mlmPackageUpgradeHistory->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                            $mlmPackageUpgradeHistory->save();

                            $uplineDistDB->setRankId($upgradedPackage->getPackageId());
                            $uplineDistDB->setRankCode($upgradedPackage->getPackageName());
                            $uplineDistDB->save();
                        }
                    }

                    if ($isUpgraded == false && $promotePackageIdNeeded != null && $promotePackageQtyNeeded != null && $promotePackageUpgradeTo != null) {
                        //var_dump("222222222");
                        //var_dump("<br>");
                        $packagePrice = MlmPackagePeer::retrieveByPK($promotePackageIdNeeded);
                        $totalDistWithPackage = $this->getTotalPackages($sponsorUplineDistId, $packagePrice->getPrice());

                        //var_dump("totalDistWithPackage:".$totalDistWithPackage);
                        //var_dump("<br>");
                        //var_dump("promotePackageQtyNeeded:".$promotePackageQtyNeeded);
                        //var_dump("<br>");
                        if ($totalDistWithPackage >= $promotePackageQtyNeeded) {
                            if ($uplinePackageDB->getPackageId() == 5 || $uplinePackageDB->getPackageId() == 6 || $uplinePackageDB->getPackageId() == 7) {

                            } else {
                                $upgradedPackage = MlmPackagePeer::retrieveByPK($promotePackageUpgradeTo);
                                $promotePackage = MlmPackagePeer::retrieveByPK($promotePackageIdNeeded);

                                $mlmPackageUpgradeHistory = new MlmPackageUpgradeHistory();
                                $mlmPackageUpgradeHistory->setDistId($sponsorUplineDistId);
                                $mlmPackageUpgradeHistory->setTransactionCode(Globals::ACCOUNT_LEDGER_ACTION_PACKAGE_PROMO);
                                $mlmPackageUpgradeHistory->setAmount(0);
                                $mlmPackageUpgradeHistory->setPackageId($upgradedPackage->getPackageId());
                                $mlmPackageUpgradeHistory->setStatusCode(Globals::STATUS_ACTIVE);
                                $mlmPackageUpgradeHistory->setRemarks("PACKAGE UPGRADED FROM " . $uplinePackageDB->getPackageName() . " => " . $upgradedPackage->getPackageName() . ", PROMOTE PACKAGE NEEDED:" . $promotePackageQtyNeeded . ", PACKAGE NAME:" . $promotePackage->getPackageName() . ", TOTAL PACKAGE:" . $totalDistWithPackage);
                                $mlmPackageUpgradeHistory->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                                $mlmPackageUpgradeHistory->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                                $mlmPackageUpgradeHistory->save();

                                $uplineDistDB->setRankId($upgradedPackage->getPackageId());
                                $uplineDistDB->setRankCode($upgradedPackage->getPackageName());
                                $uplineDistDB->save();
                            }
                        }
                    }
                    $con->commit();
                } catch (PropelException $e) {
                    $con->rollback();
                    throw $e;
                }
                $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Package upgraded successful."));

                return $this->redirect('/member/packageUpgrade');
            }
        } else {
            $c = new Criteria();
            $c->add(MlmPackagePeer::PUBLIC_PURCHASE, 1);
            $c->addAscendingOrderByColumn(MlmPackagePeer::PRICE);
            $packageDBs = MlmPackagePeer::doSelect($c);

            $distDB = MlmDistributorPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $this->forward404Unless($distDB);

            $distPackage = MlmPackagePeer::retrieveByPK($distDB->getRankId());

            $this->systemCurrency = $this->getAppSetting(Globals::SETTING_SYSTEM_CURRENCY);
            $this->pointAvailable = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_EPOINT);
            $this->ecashAvailable = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);
            $this->packageDBs = $packageDBs;
            $this->distPackage = $distPackage;
            $this->distDB = $distDB;
            //$this->highestPackageDB = $highestPackageDB;
        }
    }

    /************************************************************************************************************************
     * function
     ************************************************************************************************************************/
    function getDistributorIdByCode($sponsorCode)
    {
        $userId = 0;

        $c = new Criteria();
        $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $sponsorCode);
        //$c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $existUser = MlmDistributorPeer::doSelectOne($c);

        if ($existUser) {
            $userId = $existUser->getDistributorId();
        }

        return $userId;
    }

    function getDistributorInformation($distCode)
    {
        $c = new Criteria();

        $c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $distCode);
        //$c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $distDB = MlmDistributorPeer::doSelectOne($c);
        if (!$distDB) {
            return null;
        }

        return $distDB;
    }

    function getCommissionBalance($distributorId, $commissionType)
    {
        $query = "SELECT SUM(credit-debit) AS SUB_TOTAL FROM mlm_dist_commission_ledger WHERE dist_id = " . $distributorId . " AND commission_type = '" . $commissionType . "'";

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["SUB_TOTAL"] != null) {
                return $arr["SUB_TOTAL"];
            } else {
                return 0;
            }
        }
        return 0;
    }

    function revalidatePairing($distributorId, $leftRight)
    {
        $balance = $this->getPairingBalance($distributorId, $leftRight);

        $c = new Criteria();
        $c->add(MlmDistPairingPeer::DIST_ID, $distributorId);
        $tbl_account = MlmDistPairingPeer::doSelectOne($c);

        if (!$tbl_account) {
            $tbl_account = new MlmDistPairing();
            $tbl_account->setDistId($distributorId);
            $tbl_account->setLeftBalance(0);
            $tbl_account->setRightBalance(0);
        }
        if (Globals::PLACEMENT_LEFT == $leftRight) {
            $tbl_account->setLeftBalance($balance);
        } else if (Globals::PLACEMENT_RIGHT == $leftRight) {
            $tbl_account->setRightBalance($balance);
        }

        $tbl_account->save();
    }

    function getPairingBalance($distributorId, $leftRight)
    {
        $query = "SELECT SUM(credit-debit) AS SUB_TOTAL FROM mlm_dist_pairing_ledger WHERE dist_id = " . $distributorId . " AND left_right = '" . $leftRight . "'";

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        $count = 0;
        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["SUB_TOTAL"] != null) {
                return $arr["SUB_TOTAL"];
            } else {
                return 0;
            }
        }
        return 0;
    }

    function getAccountBalance($distributorId, $accountType)
    {
        $query = "SELECT SUM(credit-debit) AS SUB_TOTAL FROM mlm_account_ledger WHERE dist_id = " . $distributorId . " AND account_type = '" . $accountType . "'";

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["SUB_TOTAL"] != null) {
                return $arr["SUB_TOTAL"];
            } else {
                return 0;
            }
        }
        return 0;
    }

    function checkHasChild($distId)
    {
        $c = new Criteria();
        $c->add(MlmDistributorPeer::UPLINE_DIST_ID, $distId);
        $c->addAnd(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $list = MlmDistributorPeer::doSelect($c);
        if ($list) {
            return true;
        }
        return false;
    }

    function doActivateAccount($uplineDistId, $sponsorId, $packageId, $paymentType)
    {
        $packageDB = MlmPackagePeer::retrieveByPK($packageId);
        $this->forward404Unless($packageDB);
        $packageAmount = $packageDB->getPrice();

        /* ****************************************************
         * get distributor last account ledger epoint balance
         * ***************************************************/
        $c = new Criteria();
        $c->add(MlmAccountLedgerPeer::DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
        if ("epoint" == $paymentType) {
            $c->add(MlmAccountLedgerPeer::ACCOUNT_TYPE, Globals::ACCOUNT_TYPE_EPOINT);
        } else if ("ecash" == $paymentType) {
            $c->add(MlmAccountLedgerPeer::ACCOUNT_TYPE, Globals::ACCOUNT_TYPE_ECASH);
        }
        $c->addDescendingOrderByColumn(MlmAccountLedgerPeer::CREATED_ON);
        $accountLedgerDB = MlmAccountLedgerPeer::doSelectOne($c);
        if (!$accountLedgerDB) {
            $accountLedgerDB = new MlmAccountLedger();
            $accountLedgerDB->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $accountLedgerDB->setAccountType(Globals::ACCOUNT_TYPE_EPOINT);
            $accountLedgerDB->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_REGISTER);
            $accountLedgerDB->setRemark("");
            $accountLedgerDB->setCredit(0);
            $accountLedgerDB->setDebit(0);
            $accountLedgerDB->setBalance(0);
            $accountLedgerDB->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $accountLedgerDB->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $accountLedgerDB->save();
        }

        $sponsorAccountBalance = $accountLedgerDB->getBalance();

        /* ****************************************************
         * get sponsored distributor and user
         * ***************************************************/
        $c = new Criteria();
        $c->add(MlmDistributorPeer::DISTRIBUTOR_ID, $sponsorId);
        $c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_PENDING);
        $sponsoredDistDB = MlmDistributorPeer::doSelectOne($c);
        $this->forward404Unless($sponsoredDistDB);

        $userDB = AppUserPeer::retrieveByPK($sponsoredDistDB->getUserId());
        $this->forward404Unless($userDB);

        /* ****************************************************
         * update sponsored distributor and user
         * ***************************************************/
        $sponsoredDistDB->setRankId($packageDB->getPackageId());
        $sponsoredDistDB->setRankCode($packageDB->getPackageName());
        $sponsoredDistDB->setInitRankId($packageDB->getPackageId());
        $sponsoredDistDB->setInitRankCode($packageDB->getPackageName());
        $sponsoredDistDB->setStatusCode(Globals::STATUS_ACTIVE);
        $sponsoredDistDB->setPackagePurchaseFlag("Y");
        $sponsoredDistDB->setActiveDatetime(date("Y/m/d h:i:s A"));
        $sponsoredDistDB->setActivatedBy($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $sponsoredDistDB->save();

        $userDB->setStatusCode(Globals::STATUS_ACTIVE);
        $userDB->save();

        /**************************************/
        /*  Direct REFERRAL Bonus For Upline
        /**************************************/
        $uplineDistDB = MlmDistributorPeer::retrieveByPK($uplineDistId);
        if ($uplineDistDB) {
            //if ($uplineDistDB->getIbRankId() != null) {
            if ($uplineDistDB->getIsIb() == Globals::YES) {
                $directSponsorPercentage = $uplineDistDB->getIbCommission() * 100;
                $directSponsorBonusAmount = $directSponsorPercentage * $packageDB->getPrice() / 100;
            } else {
                $uplineDistPackage = MlmPackagePeer::retrieveByPK($uplineDistDB->getRankId());
                $directSponsorPercentage = $uplineDistPackage->getCommission();
                $directSponsorBonusAmount = $directSponsorPercentage * $packageDB->getPrice() / 100;
            }
            $totalBonusPayOut = $directSponsorPercentage;

            $this->doSaveAccount($sponsorId, Globals::ACCOUNT_TYPE_ECASH, 0, 0, Globals::ACCOUNT_LEDGER_ACTION_REGISTER, "");
            $this->doSaveAccount($sponsorId, Globals::ACCOUNT_TYPE_EPOINT, 0, 0, Globals::ACCOUNT_LEDGER_ACTION_REGISTER, "");
            /* ****************************************************
           * Update upline distributor account
           * ***************************************************/
            $sponsorAccountBalance = $sponsorAccountBalance - $packageDB->getPrice();

            $mlm_account_ledger = new MlmAccountLedger();
            $mlm_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            if ("epoint" == $paymentType) {
                $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_EPOINT);
            } else if ("ecash" == $paymentType) {
                $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
            }
            $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_REGISTER);
            $mlm_account_ledger->setRemark("PACKAGE PURCHASE (" . $packageDB->getPackageName() . ")");
            $mlm_account_ledger->setCredit(0);
            $mlm_account_ledger->setDebit($packageDB->getPrice());
            $mlm_account_ledger->setBalance($sponsorAccountBalance);
            $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_account_ledger->save();

            /******************************/
            /*  Direct Sponsor Bonus
            /******************************/
            $firstForDRB = true;
            while ($totalBonusPayOut <= Globals::TOTAL_BONUS_PAYOUT) {
                $distAccountEcashBalance = $this->getAccountBalance($uplineDistId, Globals::ACCOUNT_TYPE_ECASH);

                $mlm_account_ledger = new MlmAccountLedger();
                $mlm_account_ledger->setDistId($uplineDistId);
                $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
                $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_DRB);
                $mlm_account_ledger->setRemark("PACKAGE PURCHASE (" . $packageDB->getPackageName() . ") " . $directSponsorPercentage . "% (" . $sponsoredDistDB->getDistributorCode() . ")");
                $mlm_account_ledger->setCredit($directSponsorBonusAmount);
                $mlm_account_ledger->setDebit(0);
                $mlm_account_ledger->setBalance($distAccountEcashBalance + $directSponsorBonusAmount);
                $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_account_ledger->save();

                //$this->revalidateAccount($uplineDistId, Globals::ACCOUNT_TYPE_ECASH);

                /******************************/
                /*  Commission
                /******************************/
                $dsbBalance = $this->getCommissionBalance($uplineDistId, Globals::COMMISSION_TYPE_DRB);

                $sponsorDistCommissionledger = new MlmDistCommissionLedger();
                $sponsorDistCommissionledger->setDistId($uplineDistId);
                $sponsorDistCommissionledger->setCommissionType(Globals::COMMISSION_TYPE_DRB);
                $sponsorDistCommissionledger->setTransactionType(Globals::COMMISSION_LEDGER_REGISTER);
                $sponsorDistCommissionledger->setCredit($directSponsorBonusAmount);
                $sponsorDistCommissionledger->setDebit(0);
                $sponsorDistCommissionledger->setStatusCode(Globals::STATUS_ACTIVE);
                $sponsorDistCommissionledger->setBalance($dsbBalance + $directSponsorBonusAmount);
                if ($firstForDRB == true) {
                    $sponsorDistCommissionledger->setRemark("DRB FOR PACKAGE PURCHASE " . $directSponsorPercentage . "% (" . $packageDB->getPackageName() . ") for " . $sponsoredDistDB->getDistributorCode());
                    $firstForDRB = false;
                } else {
                    $sponsorDistCommissionledger->setRemark("GRB FOR PACKAGE PURCHASE " . $directSponsorPercentage . "% (" . $packageDB->getPackageName() . ") for " . $sponsoredDistDB->getDistributorCode());
                }
                $sponsorDistCommissionledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $sponsorDistCommissionledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $sponsorDistCommissionledger->save();

                //$this->revalidateCommission($uplineDistId, Globals::COMMISSION_TYPE_DRB);
                //var_dump("==>1");
                if ($totalBonusPayOut < Globals::TOTAL_BONUS_PAYOUT) {
                    //var_dump("==>2");
                    $checkCommission = true;
                    $uplineDistId = $uplineDistDB->getUplineDistId();
                    while ($checkCommission == true) {
                        //var_dump("==>3**".$uplineDistId);
                        $uplineDistDB = MlmDistributorPeer::retrieveByPK($uplineDistId);

                        //var_dump("==>3$$".$uplineDistId);
                        $this->forward404Unless($uplineDistDB);

                        if ($uplineDistDB->getIsIb() == Globals::YES) {
                            /*if ($uplineDistDB->getIbRankId() != null) {
                                $uplineDistPackage = MlmIbPackagePeer::retrieveByPK($uplineDistDB->getIbRankId());
                            } else {
                                $uplineDistPackage = MlmPackagePeer::retrieveByPK($uplineDistDB->getRankId());
                            }*/
                            $directSponsorPercentage = $uplineDistDB->getIbCommission() * 100;
                        } else {
                            $uplineDistPackage = MlmPackagePeer::retrieveByPK($uplineDistDB->getRankId());
                            $directSponsorPercentage = $uplineDistPackage->getCommission();
                        }
                        if ($directSponsorPercentage > $totalBonusPayOut) {
                            //var_dump("==>6");
                            $directSponsorPercentage = $directSponsorPercentage - $totalBonusPayOut;
                            $totalBonusPayOut += $directSponsorPercentage;
                            if ($totalBonusPayOut > Globals::TOTAL_BONUS_PAYOUT) {
                                //var_dump("==>7");
                                $directSponsorPercentage = $directSponsorPercentage - ($totalBonusPayOut - Globals::TOTAL_BONUS_PAYOUT);
                            }
                        } else {
                            //var_dump("==>8");
                            $uplineDistId = $uplineDistDB->getUplineDistId();
                            continue;
                        }

                        $directSponsorBonusAmount = $directSponsorPercentage * $packageDB->getPrice() / 100;
                        $checkCommission == false;
                        break;
                        //var_dump("==>9");
                    }
                } else {
                    break;
                    //var_dump("==>^^");
                }
            }
        }

    }

    function getAppSetting($parameter)
    {
        $result = "";
        $c = new Criteria();
        $c->add(AppSettingPeer::SETTING_PARAMETER, $parameter);
        $settingDB = AppSettingPeer::doSelectOne($c);
        if ($settingDB) {
            $result = $settingDB->getSettingValue();
        }
        return $result;
    }

    function doSaveAccount($distId, $accountType, $credit, $debit, $transactionType, $remarks)
    {
        $mlm_account_ledger = new MlmAccountLedger();
        $mlm_account_ledger->setDistId($distId);
        $mlm_account_ledger->setAccountType($accountType);
        $mlm_account_ledger->setTransactionType($transactionType);
        $mlm_account_ledger->setRemark($remarks);
        $mlm_account_ledger->setCredit($credit);
        $mlm_account_ledger->setDebit($debit);
        $mlm_account_ledger->setBalance($credit - $debit);
        $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_account_ledger->save();
    }

    function getPlacementDistributorInformation($uplineDistId, $placeLocation)
    {
        $c = new Criteria();
        $c->add(MlmDistributorPeer::TREE_UPLINE_DIST_ID, $uplineDistId);
        $c->add(MlmDistributorPeer::PLACEMENT_POSITION, $placeLocation);
        //$c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);

        $placeDB = MlmDistributorPeer::doSelectOne($c);
        return $placeDB;
    }

    function getTotalPosition($distId, $position)
    {
        $c = new Criteria();
        $c->add(MlmDistributorPeer::PLACEMENT_POSITION, $position);
        $c->add(MlmDistributorPeer::DISTRIBUTOR_ID, $distId, Criteria::NOT_EQUAL);
        $c->add(MlmDistributorPeer::PLACEMENT_TREE_STRUCTURE, "%|" . $distId . "|%", Criteria::LIKE);
        //$c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);

        $totalDis = MlmDistributorPeer::doCount($c);
        return $totalDis;
    }

    function getDsbAmount($distributorId, $date)
    {
        $query = "SELECT SUM(credit) AS SUB_TOTAL FROM mlm_dist_commission_ledger WHERE dist_id = " . $distributorId
                 . " AND commission_type = '" . Globals::ACCOUNT_LEDGER_ACTION_DRB . "'";

        $query .= " AND created_on >= '" . $date . " 00:00:00'";
        $query .= " AND created_on <= '" . $date . " 23:59:59'";

        //var_dump($query);
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["SUB_TOTAL"] != null) {
                return $arr["SUB_TOTAL"];
            } else {
                return 0;
            }
        }
        return 0;
    }

    function toFreezeAndCalculateInvestmentPackage($batchId)
    {
        $connection = Propel::getConnection();

        $query = "UPDATE mlm_dist_investment_package set bonus_release = '" . Globals::STATUS_PROCESSING . "' WHERE status_code = '" . Globals::STATUS_ACTIVE . "'
            AND bonus_release = '" . Globals::STATUS_PENDING . "' AND batch_id = " . $batchId;

        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        $query = "SELECT SUM(total_package) AS SUB_TOTAL FROM mlm_dist_investment_package
            WHERE bonus_release  = '" . Globals::STATUS_PROCESSING . "' AND batch_id = " . $batchId;

        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["SUB_TOTAL"] != null) {
                return $arr["SUB_TOTAL"];
            } else {
                return 0;
            }
        }
        return 0;
    }

    function unFreezeInvestmentPackage()
    {
        $connection = Propel::getConnection();

        $query = "UPDATE mlm_dist_investment_package set bonus_release = '" . Globals::STATUS_COMPLETE . "' WHERE status_code = '" . Globals::STATUS_ACTIVE . "'
            AND bonus_release = '" . Globals::STATUS_PROCESSING . "'";

        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
    }

    function updateDistPairingLeader($distId, $position, $debit, $remark = "PAIRED", $transactionType = Globals::PAIRING_LEDGER_PAIRED)
    {
        /*$c = new Criteria();
        $c->add(MlmDistPairingLedgerPeer::DIST_ID, $distId);
        $c->add(MlmDistPairingLedgerPeer::LEFT_RIGHT, $position);
        $c->addDescendingOrderByColumn(MlmDistPairingLedgerPeer::CREATED_ON);
        $sponsorDistPairingLedgerDB = MlmDistPairingLedgerPeer::doSelectOne($c);

        $legBalance = 0;
        if ($sponsorDistPairingLedgerDB) {
            $legBalance = $sponsorDistPairingLedgerDB->getBalance();
        }*/
        $legBalance = $this->findPairingLedgers($distId, $position, null);
        // update pairing balance
        $distPairingledger = new MlmDistPairingLedger();
        $distPairingledger->setDistId($distId);
        $distPairingledger->setLeftRight($position);
        $distPairingledger->setTransactionType($transactionType);
        $distPairingledger->setCredit(0);
        $distPairingledger->setDebit($debit);
        $distPairingledger->setBalance($legBalance - $debit);
        $distPairingledger->setRemark($remark);
        $distPairingledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $distPairingledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $distPairingledger->save();

        $this->revalidatePairing($distId, $position);
    }

    function flushDistPairingLeader($distId, $position, $minBalance, $remark)
    {
        $distPairingledger = new MlmDistPairingLedger();
        $distPairingledger->setDistId($distId);
        $distPairingledger->setLeftRight($position);
        $distPairingledger->setTransactionType(Globals::PAIRING_LEDGER_FLUSH);
        $distPairingledger->setCredit(0);
        $distPairingledger->setDebit($minBalance);
        $distPairingledger->setBalance(0);
        $distPairingledger->setRemark($remark);
        $distPairingledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $distPairingledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $distPairingledger->save();

        $this->revalidatePairing($distId, $position);
    }

    function getRbDetailByMonth($distributorId, $month, $year)
    {
        $dateUtil = new DateUtil();

        $d = $dateUtil->getMonth($month, $year);
        $firstOfMonth = date('Y-m-j', $d["first_of_month"]) . " 00:00:00";
        $lastOfMonth = date('Y-m-j', $d["last_of_month"]) . " 23:59:59";

        $query = "SELECT SUM(bonus.credit-bonus.debit) AS SUB_TOTAL FROM mlm_dist_commission_ledger bonus
                        WHERE 1=1 "
                 . " AND bonus.commission_type = '" . Globals::COMMISSION_TYPE_DRB . "'"
                 . " AND bonus.created_on >= '" . $firstOfMonth . "' AND bonus.created_on <= '" . $lastOfMonth . "'";

        if ($distributorId != null) {
            $query = $query . " AND bonus.dist_id = " . $distributorId;
        }
        //var_dump($query);

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["SUB_TOTAL"] != null) {
                return $arr["SUB_TOTAL"];
            } else {
                return 0;
            }
        }
        return 0;
    }

    function getPairingDetailByMonth($distributorId, $month, $year)
    {
        $dateUtil = new DateUtil();

        $d = $dateUtil->getMonth($month, $year);
        $firstOfMonth = date('Y-m-j', $d["first_of_month"]) . " 00:00:00";
        $lastOfMonth = date('Y-m-j', $d["last_of_month"]) . " 23:59:59";

        $query = "SELECT SUM(bonus.credit-bonus.debit) AS SUB_TOTAL FROM mlm_dist_commission_ledger bonus
                        WHERE 1=1 "
                 . " AND bonus.commission_type = '" . Globals::COMMISSION_TYPE_GDB . "'"
                 . " AND bonus.created_on >= '" . $firstOfMonth . "' AND bonus.created_on <= '" . $lastOfMonth . "'";

        if ($distributorId != null) {
            $query = $query . " AND bonus.dist_id = " . $distributorId;
        }
        //var_dump($query);

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["SUB_TOTAL"] != null) {
                return $arr["SUB_TOTAL"];
            } else {
                return 0;
            }
        }
        return 0;
    }

    function getTotalInvestmentPackageInWallet($distId)
    {
        $query = "SELECT SUM(total_package) AS _SUM FROM mlm_dist_investment_package";

        $query .= " WHERE dist_id = " . $distId;
        $query .= " AND status_code = '" . Globals::STATUS_ACTIVE . "'";
        //var_dump($query);

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["_SUM"] != null) {
                return $arr["_SUM"];
            } else {
                return 0;
            }
        }
        return 0;
    }

    function getTotalPackageInTheGame()
    {
        $query = "SELECT SUM(total_package) AS _SUM FROM mlm_dist_investment_package
                WHERE status_code = '" . Globals::STATUS_ACTIVE . "'
                    AND bonus_release <> '" . Globals::STATUS_PENDING . "' ";

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["_SUM"] != null) {
                return $arr["_SUM"];
            } else {
                return 0;
            }
        }
        return 0;
    }

    function getTotalCurrentInvestmentPackageSold()
    {
        $query = "SELECT SUM(total_package) AS _SUM FROM mlm_dist_investment_package";

        $query .= " WHERE status_code = '" . Globals::STATUS_ACTIVE . "' AND bonus_release = '" . Globals::STATUS_PENDING . "'";
        //var_dump($query);

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["_SUM"] != null) {
                return $arr["_SUM"];
            } else {
                return 0;
            }
        }
        return 0;
    }

    function getTotalInvestmentPackageSold($date)
    {
        $query = "SELECT SUM(total_package) AS _SUM FROM mlm_dist_investment_package package";

        $query .= " LEFT JOIN mlm_distributor dist ON dist.distributor_id = package.dist_id";
        $query .= " WHERE package.status_code = '" . Globals::STATUS_ACTIVE . "' AND dist.status_code = '" . Globals::STATUS_ACTIVE . "' AND package.dist_id <> " . Globals::SYSTEM_COMPANY_DIST_ID;
        if ($date != null) {
            $dateFrom = $date . " 00:00:00";
            $dateTo = $date . " 23:59:59";

            $query .= " AND package.created_on >= '" . $dateFrom . "' AND package.created_on <='" . $dateTo . "'";
        }
        //var_dump($query);

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["_SUM"] != null) {
                return $arr["_SUM"];
            } else {
                return 0;
            }
        }
        return 0;
    }

    function getTotalTreePackageInvested($distId)
    {
        $query = "SELECT SUM(package.total_package) AS _SUM, package.dist_id
                    FROM mlm_dist_investment_package package
                LEFT JOIN mlm_distributor dist ON dist.distributor_id = package.dist_id
                        WHERE package.status_code = '" . Globals::STATUS_ACTIVE . "'
                    AND dist.status_code = '" . Globals::STATUS_ACTIVE . "'
                    AND package.bonus_release <> '" . Globals::STATUS_PENDING . "'
                    AND tree_structure LIKE '%|" . $distId . "|%'";
        //var_dump($query);

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["_SUM"] != null) {
                return $arr["_SUM"];
            } else {
                return 0;
            }
        }
        return 0;
    }

    function getTotalFundManagementGain($distId)
    {
        $query = "SELECT SUM(credit - debit) AS _SUM
                    FROM mlm_account_ledger
                where dist_id = " . $distId . "
                AND account_type = '" . Globals::ACCOUNT_TYPE_ECASH . "'
                AND transaction_type = '" . Globals::ACCOUNT_LEDGER_ACTION_FUND_MANAGEMENT . "'";
        //var_dump($query);

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["_SUM"] != null) {
                return $arr["_SUM"];
            } else {
                return 0;
            }
        }
        return 0;
    }

    function getRankColor($packageId)
    {
        $color = "blue";

        $package = MlmPackagePeer::retrieveByPK($packageId);
        if ($package) {
            $color = $package->getColor();
        }

        return $color;
    }

    function getRankColorArr()
    {
        $packageArray = array();
        $c = new Criteria();
        $packages = MlmPackagePeer::doSelect($c);
        foreach ($packages as $package) {
            $packageArray[$package->getPackageId()] = $package->getColor();
        }

        return $packageArray;
    }

    function getLegTotalMember($distributor)
    {
        if ($distributor) {
            $c = new Criteria();
            $c->add(MlmDistributorPeer::PLACEMENT_TREE_STRUCTURE, "%|" . $distributor->getDistributorId() . "|%", Criteria::LIKE);
            $c->add(MlmDistributorPeer::STATUS_CODE, Globals::STATUS_ACTIVE);

            $totalDis = MlmDistributorPeer::doCount($c);
            return $totalDis;
        }

        return 0;
    }

    function entitiedInvestmentPackageBonus($distId)
    {
        $c = new Criteria();
        $c->add(MlmDistInvestmentPackagePeer::DIST_ID, $distId);
        $c->add(MlmDistInvestmentPackagePeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        //$c->add(MlmDistInvestmentPackagePeer::BONUS_RELEASE, Globals::STATUS_COMPLETE);

        $totalInvestmentPackage = MlmDistributorPeer::doCount($c);
        if ($totalInvestmentPackage > 0)
            return true;
        return false;
    }

    function fetchMemberWithoutUploadDocument($date)
    {
        $dateFrom = $date . " 00:00:00";
        $dateTo = $date . " 23:59:59";

        $query = "SELECT distributor_id, distributor_code, full_name, email
	                    FROM mlm_distributor where (file_bank_pass_book is null or file_proof_of_residence is null or file_nric is null)
	                    and email is not null
	                    and active_datetime >= '" . $dateFrom . "' AND created_on <= '" . $dateTo . "'
	                    and status_code = '" . Globals::STATUS_ACTIVE . "'";

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
        $resultArray = array();
        $count = 0;
        while ($resultset->next()) {
            $arr = $resultset->getRow();

            $resultArray[$count]["distributor_id"] = $arr["distributor_id"];
            $resultArray[$count]["distributor_code"] = $arr["distributor_code"];
            $resultArray[$count]["full_name"] = $arr["full_name"];
            $resultArray[$count]["email"] = $arr["email"];
            $count++;
        }
        return $resultArray;
    }

    function getMaxSponsorPackagePrice($sponsorUplineDistId)
    {
        $query = "SELECT max(package.price) as MAX_PRICE
                    FROM mlm_distributor dist
                        LEFT JOIN mlm_package package ON package.package_id = dist.rank_id
                    WHERE dist.upline_dist_id = " . $sponsorUplineDistId;

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
        $resultArray = array();
        $result = 0;
        if ($resultset->next()) {
            $arr = $resultset->getRow();

            $result = $arr["MAX_PRICE"];
        }
        return $result;
    }

    function getTotalPackages($sponsorUplineDistId, $packagePrice)
    {
        $query = "SELECT count(dist.distributor_id) AS _count FROM mlm_distributor dist
                    LEFT JOIN mlm_package pack ON pack.package_id = dist.rank_id
                WHERE dist.upline_dist_id = " . $sponsorUplineDistId . " AND pack.price >=" . $packagePrice;

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
        $resultArray = array();
        $result = 0;
        if ($resultset->next()) {
            $arr = $resultset->getRow();

            $result = $arr["_count"];
        }
        return $result;
    }

    function findCardMpReturnList($distId)
    {

        $query = "SELECT DISTINCT dist_id
	                FROM card_mp_return WHERE dist_id = " . $distId;

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
        $resultArray = array();
        $count = 0;
        while ($resultset->next()) {
            $arr = $resultset->getRow();

            $resultArray[$count]["dist_id"] = $arr["dist_id"];
            $resultArray[$count]["unrealized_profit"] = 25 * Globals::DIVIDEND_TIMES_ENTITLEMENT;
            $resultArray[$count]["realized_profit"] = $this->getRealizedProfit($arr["dist_id"]);

            $count++;
        }
        return $resultArray;
    }

    function getRealizedProfit($distId)
    {

        $query = "SELECT SUM(return_amount) AS _SUM FROM card_mp_return
                WHERE dist_id = '" . $distId . "' AND status_code = '" . Globals::DIVIDEND_STATUS_SUCCESS . "'";

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
        $resultArray = array();
        $result = 0;
        if ($resultset->next()) {
            $arr = $resultset->getRow();

            if ($arr["_SUM"] != null) {
                $result = $arr["_SUM"];
            } else {
                $result = 0;
            }
        }
        return $result;
    }

    function format2decimal($d)
    {
        return ceil($d * 100) / 100;
    }
}